{"id":322,"date":"2024-08-26T05:43:46","date_gmt":"2024-08-26T05:43:46","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=322"},"modified":"2025-05-16T09:16:33","modified_gmt":"2025-05-16T09:16:33","slug":"sql-primary-key","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/sql-primary-key\/","title":{"rendered":"SQL Primary Key"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL Primary Key Syntax<\/h2>\n\n\n\n

When creating a table: <\/strong><\/p>\n\n\n\n

CREATE TABLE table_name (\n  column1 datatype PRIMARY KEY,\n  column2 datatype\n);\n<\/pre><\/div>\n\n\n\n
For composite keys (multiple columns):<\/strong><\/p>\n\n\n\n
CREATE TABLE table_name (\n  column1 datatype,\n  column2 datatype,\n  PRIMARY KEY (column1, column2)\n);\n<\/pre><\/div>\n\n\n\n
\n
SQLite does not support dropping primary keys directly using ALTER TABLE<\/code>.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL Primary Key Example<\/h2>\n\n\n\n
SQL Primary Key on Create Table<\/h3>\n\n\n\n
This query sets up a table to store medical records with a unique record_id.<\/p>\n\n\n\n
CREATE TABLE Records_Log (\n  record_id INTEGER PRIMARY KEY,\n  patient_id INTEGER,\n  notes TEXT\n);<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Try It Out!<\/a><\/div>\n<\/div>\n\n\n\n
This query shows how to create a composite primary key using two columns.<\/p>\n\n\n\n
CREATE TABLE Appointment_Log (\n  patient_id INTEGER,\n  doctor_id INTEGER,\n  PRIMARY KEY (patient_id, doctor_id)\n);<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL Primary Key on Alter Table<\/h3>\n\n\n\n
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:<\/p>\n\n\n\n
This query creates a new table with a primary key:<\/p>\n\n\n\n
CREATE TABLE NewPatients (\n  patient_id INTEGER PRIMARY KEY,\n  first_name TEXT,\n  last_name TEXT\n);<\/pre><\/div>\n\n\n\n
This query copies the data from the old table into the new one:<\/p>\n\n\n\n
INSERT INTO NewPatients (patient_id, first_name, last_name)\nSELECT patient_id, first_name, last_name FROM Patients;<\/pre><\/div>\n\n\n\n
Then, you would drop the original table and rename the new one:<\/p>\n\n\n\n
DROP TABLE Patients;\nALTER TABLE NewPatients RENAME TO Patients;<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL Primary Key Drop a Primary Key Constraint Example<\/h3>\n\n\n\n
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):<\/p>\n\n\n\n
ALTER TABLE table_name\nDROP CONSTRAINT constraint_name;<\/pre><\/div>\n\n\n\n
\n
In SQLite, use the same workaround approach as above:<\/p>\n<\/blockquote>\n\n\n\n
    \n
  1. Create a new version of the table without the primary key.<\/li>\n\n\n\n
  2. Copy the data into it.<\/li>\n\n\n\n
  3. Drop the old table.<\/li>\n\n\n\n
  4. Rename the new one to match the original.<\/li>\n<\/ol>\n\n\n\n
    <\/p>\n\n\n\n
    SQL Primary Key Labs<\/h2>\n\n\n\n