SQL Primary Key

A Primary Key is a column (or a set of columns) that uniquely identifies each row in a table. It cannot contain NULL values, and each value must be unique. Each table can only have one primary key.

SQL Primary Key Syntax

When creating a table:

CREATE TABLE table_name (
  column1 datatype PRIMARY KEY,
  column2 datatype
);

For composite keys (multiple columns):

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  PRIMARY KEY (column1, column2)
);

SQLite does not support dropping primary keys directly using ALTER TABLE.

SQL Primary Key Example

SQL Primary Key on Create Table

This query sets up a table to store medical records with a unique record_id.

CREATE TABLE Records_Log (
  record_id INTEGER PRIMARY KEY,
  patient_id INTEGER,
  notes TEXT
);

This query shows how to create a composite primary key using two columns.

CREATE TABLE Appointment_Log (
  patient_id INTEGER,
  doctor_id INTEGER,
  PRIMARY KEY (patient_id, doctor_id)
);

SQL Primary Key on Alter Table

SQLite only supports adding a column using ALTER TABLE, but it doesn’t allow adding a primary key to an existing table. If you need to add a primary key to an existing table, you can follow this process:

This query creates a new table with a primary key:

CREATE TABLE NewPatients (
  patient_id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

This query copies the data from the old table into the new one:

INSERT INTO NewPatients (patient_id, first_name, last_name)
SELECT patient_id, first_name, last_name FROM Patients;

Then, you would drop the original table and rename the new one:

DROP TABLE Patients;
ALTER TABLE NewPatients RENAME TO Patients;

SQL Primary Key Drop a Primary Key Constraint Example

SQLite does not support dropping a primary key using ALTER TABLE DROP CONSTRAINT. However, this is how it would normally look in standard SQL (like MySQL or SQL Server):

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

In SQLite, use the same workaround approach as above:

1. Create a new version of the table without the primary key.
2. Copy the data into it.
3. Drop the old table.
4. Rename the new one to match the original.

SQL Primary Key Labs