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;