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

SQLite - DROP CONSTRAINT

DROP a UNIQUE Constraint

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

For example, to drop UNIQUE constraint from 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, 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 a 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 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, Salary DECIMAL(18,2) ); INSERT INTO Employee SELECT * FROM Employee_old; COMMIT; PRAGMA foreign_keys=on;

DROP a FOREIGN KEY Constraint

SQLite does not support ALTER TABLE or DROP statement to drop an existing FOREIGN 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 FOREIGN 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 FOREIGN KEY constraint from 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) ); INSERT INTO Contact_Info SELECT * FROM Contact_Info_old; COMMIT; PRAGMA foreign_keys=on;

DROP a CHECK Constraint

SQLite does not support ALTER TABLE or DROP statement to drop an existing CHECK 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 CHECK constraint dropped
  • Copy the data from renamed table to new table
  • In the end, turn on the foreign key constraints

For example, to drop CHECK constraint from 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, Salary DECIMAL(18,2) ); INSERT INTO Employee SELECT * FROM Employee_old; COMMIT; PRAGMA foreign_keys=on;

DROP a DEFAULT Constraint

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

For example, to drop DEFAULT constraint from 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), 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.