SQLite - PRIMARY KEY

The SQLite PRIMARY KEY constraint is used to ensure that all values in a column of a table are UNIQUE and NOT NULL. A table can have only one PRIMARY KEY constraint, and a primary key can consist of single or multiple columns (fields).

SQLite PRIMARY KEY constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which PRIMARY KEY constraint is applied on column EmpID.

CREATE TABLE Employee (
  EmpID INT NOT NULL PRIMARY KEY,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

Alternatively, it can also be created as mentioned below:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

To provide a name to PRIMARY KEY constraint, and to define a PRIMARY KEY constraint on multiple columns (say EmpID and Name), the statement is given below:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CONSTRAINT PK_Employee PRIMARY KEY(EmpID, Name)
);

SQLite PRIMARY KEY constraint with ALTER TABLE

SQLite does not support ALTER TABLE statement to add a PRIMARY KEY to the existing table. However this can be achieved using following steps:

• The foreign key constraint should be checked off
• Rename the table to some other name
• Create a new table with same structure but PRIMARY KEY constraint added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce PRIMARY KEY constraint on EmpID column of the existing table Employee, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Employee RENAME TO Employee_old;

CREATE TABLE Employee (
  EmpID INT NOT NULL PRIMARY KEY,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

DROP PRIMARY KEY constraint

SQLite does not support ALTER TABLE or DROP statement to drop an existing PRIMARY KEY constraint from a table. To achieve this, the following steps can be used:

• The foreign key constraint should be checked off
• Rename the table to some other name
• Create a new table with same structure but PRIMARY KEY constraint dropped
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to drop PRIMARY KEY constraint from EmpID column of the existing table Employee, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Employee RENAME TO Employee_old;

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;