SQLite Tutorial SQLite Advanced SQLite Database SQLite References
SQLite Tutorial SQLite Advanced SQLite Database SQLite References

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;

❮ SQLite Keywords
AlphaCodingSkills
AlphaCodingSkills Android App
Facebook Page Twitter Page LinkedIn Page
Learning Tutorials
Learning Tutorials
Useful Links
Python Libraries
About Us  |  Privacy Policy  |  Contact Us
AlphaCodingSkills is a online learning portal that provides tutorials on Python, Java, C++, C, C#, PHP, R, Ruby, Rust, Scala, Swift, Perl, SQL, Data Structures and Algorithms. Tutorials, examples, references and content of the website are reviewed and simplified continuously to improve comprehensibility and eliminate any possible error. We use cookies to ensure best browsing experience on our website. While using this website, you acknowledge to have read and accepted our cookie and privacy policy.