[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!

[T-SQL] Table Constraints

Table constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing data integrity.

Types of Constraints:

1. NOT NULL enforces not to accept NULL values
2. UNIQUE assures that the data is unique in the column (including one NULL).
3. PRIMARY KEY uniquely identifies each record in the table without allowing NULL.
4. FOREIGN KEY constraints the data to be inserted that is available in table's column it points to.
5. CHECK is used to limit the value range that can be placed in that column.
6. DEFAULT is used to insert a default value to all new records, if no value is specified.