Primary Keys

SQLite primary key is a simple field or a combination of fields which is used to uniquely define a record. A table can have only one primary key.

A primary key should not be a NULL value.

Create Primary Key

Primary key is generally created at the time of creating the table. We define the primary key while executing CREATE TABLE statement.

Syntax:

 CREATE TABLE table_name  

(  

column1 datatype [ NULL | NOT NULL ],  

column2 datatype [ NULL | NOT NULL ],  

......  

CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)  

); 

Parameter explanation:

• table_name: It specifies the name of the table that you want to create.
• column1, column2: It specifies the columns that you want to create in the table.
• constraint_name: It specifies the name of the primary key.
• pk_col1, pk_col2, … pk_col_n: It specifies the columns that make up the primary key.

Example:

Create a table “WORKERS” where worker_id is primary key.

CREATE TABLE WORKERS  

( worker_id INTEGER PRIMARY KEY,  

last_name VARCHAR NOT NULL,  

first_name VARCHAR,  

join_date DATE  

);

Add Primary Key

It is used when you have not defined primary key at CREATE TABLE statement and you have to add a primary key later.

You cannot use ALTER TABLE statement to create a primary key. Instead you have to create a new table with primary key and copy all data from old table.

Syntax:

  PRAGMA foreign_keys=off;  

BEGIN TRANSACTION;  

ALTER TABLE table_name RENAME TO old_table;  

CREATE TABLE table_name  

(  

column1 datatype [ NULL | NOT NULL ],  

column2 datatype [ NULL | NOT NULL ],  

...  

CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)  

);  

INSERT INTO table_name SELECT * FROM old_table;  

COMMIT;  

PRAGMA foreign_keys=on;  

Parameter explanation:

• table_name: It specifies the name of the table from which you want to remove the primary key from.
• old_table: This specifies the name of the original table that will be deleted after you have created the new table with the primary key removed.

Example:

Suppose, we have an existing table “engineers” with primary key “engineer_id” and we have to remove that primary key.

CREATE TABLE engineers  

(engineer_id INTEGER,  

engineerr_name VARCHAR NOT NULL,  

address VARCHAR,  

city VARCHAR,  

CONSTRAINT engineers_pk PRIMARY KEY (engineer_id)  

);

Now, run the following command to drop the primary key.

PRAGMA foreign_keys=off;  
BEGIN TRANSACTION;  
ALTER TABLE engineers RENAME TO old_engineers;  
CREATE TABLE engineers  
(  
engineer_id INTEGER,  
engineer_name VARCHAR NOT NULL,  
address VARCHAR,  
city VARCHAR  
);  
INSERT INTO engineers SELECT * FROM old_engineers;  
COMMIT;  
PRAGMA foreign_keys=on;  

Primary key is now dropped from the existing “engineers” table. But the original table is now renamed as “old_engineers”.

Now drop the “old_engineers” table.

DROP TABLE old_engineers;