Constraints in sql | Sql constraint | Drop constraint in sql - sql - sql tutorial - learn sql
- Constraints can be placed on a table to limit the type of data that can go into a table.
- Since we can specify constraints on a table, there needs to be a way to remove this constraint as well.
- In SQL, this is done via the ALTER TABLE statement.
- Drop of constrain can remove primary, foreign and other keys from the column
- SQL > ALTER TABLE > Drop Constraint Syntax
- The SQL syntax to remove a constraint from a table is,
ALTER TABLE "table_name"
DROP [CONSTRAINT|INDEX] "CONSTRAINT_NAME";
- Let's look at the example. Assuming our starting point is the Employee table created in the CREATE TABLE section:
Table Employee
| Column Name | Data Type |
|---|---|
| First_Name | char(50) |
| Last_Name | char(50) |
| Address | char(50) |
| City | char(50) |
| Country | char(25) |
| Birth_Date | datetime |
- Assume we want to drop the UNIQUE constraint on the "Address" column, and the name of the constraint is "Con_First." To do this, we type in the following:
MySQL:
ALTER TABLE Employee DROP INDEX Con_First;
- Note that MySQL uses DROP INDEX for index-type constraints such as UNIQUE.
Oracle:
ALTER TABLE Employee DROP CONSTRAINT Con_First;
SQL Server:
ALTER TABLE Employee DROP CONSTRAINT Con_First;