SQLite - ADD CONSTRAINT

ADD UNIQUE Constraint

SQLite does not support ALTER TABLE statement to add UNIQUE constraint 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 UNIQUE constraint added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce UNIQUE constraint on EmpID column of an 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 (
  EEmpID INT NOT NULL UNIQUE,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

ADD PRIMARY KEY Constraint

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 an 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;

ADD FOREIGN KEY Constraint

SQLite does not support ALTER TABLE statement to add a FOREIGN 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 FOREIGN 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 FOREIGN KEY constraint on EmpID column of an existing table Contact_Info, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Contact_Info RENAME TO Contact_Info_old;

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

INSERT INTO Contact_Info SELECT * FROM Contact_Info_old;

COMMIT;

PRAGMA foreign_keys=on;

ADD CHECK Constraint

SQLite does not support ALTER TABLE statement to add a CHECK constraint 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 CHECK constraint added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce CHECK constraint on Age column of an 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 CHECK (Age >= 21),
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

ADD DEFAULT Constraint

SQLite does not support ALTER TABLE statement to add DEFAULT constraint 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 DEFAULT constraint added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce DEFAULT constraint on City column of an 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) DEFAULT 'London',
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;