[T-SQL] Alter Table - Add/Drop Constraint Query

It is a good practice to identify and apply constraints while creating the table, but in real scenario, we usually require to add constraint on existing column or while adding a new column in existing table. For this purpose, a simple and quick solution is to do it through query. Below is how you achieve it:

Add Constraint on Existing Column:

ALTER TABLE <table-name>
ADD CONSTRAINT <constraint-name> 
PRIMARY KEY (<column-name>)

where, "<constraint-name>" is a user defined constraint name to refer it properly.

The above syntax could be used for UNIQUE, DEFAULT and CHECK constraints, but for FOREIGN KEY constraint, the query would be as follows:

ALTER TABLE <target-table-name>
ADD CONSTRAINT <constraint-name>
FOREIGN KEY ( <target-column-name> )
REFERENCES <source-table-name> ( <source-column-name> )

Add Constraint on New Column:

ALTER TABLE <table-name>
ADD <column-name> BIGINT IDENTITY
CONSTRAINT <constraint-name> PRIMARY KEY

where, "<constraint-name>" is a user defined constraint name and "IDENTITY"  is optional if you want to make this identity with default seed and increment value set to 1.


The above syntax could be used for UNIQUE, DEFAULT and CHECK constraints, but for FOREIGN KEY constraint, the query would be as follows:

ALTER TABLE <target-table-name>
ADD <target-column-name> BIGINT
CONSTRAINT <constraint-name>
FOREIGN KEY ( <target-column-name> )
REFERENCES <source-table-name> ( <source-column-name> )

Drop Constraint from Existing Column:

To drop a constraint is very simple:

ALTER TABLE <table-name> 
DROP CONSTRAINT <constraint-name>

Enjoy!