SQL: Constraints

Constraint can be used to specify rules data type of table. Constraint can be specified while creating or altering the table statement. If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Syntax:

1 2 3 4 5 6 7

CREATE TABLE tableName ( columnName1 dataType(size) constraintName, columnName2 dataType(size) constraintName, columnName3 dataType(size) constraintName, .... )

Constraint Types and Examples:

1. NOT NULL: Indicates that a column cannot store NULL value. By default, a table column can hold NULL values.

Syntax:

1 2	ALTER TABLE <tableName> ADD CONSTRAINT <constraintName> NOT NULL(columnName1, columnName2)

[Using CONSTRAINT <constraint name> allows easy edit/ drop of the constraint when same constraint is used on multiple columns]

2. UNIQUE: Ensures that each row for a column must have a unique value. The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

Syntax:

1 2	ALTER TABLE <tableName> ADD UNIQUE (<columnName>)

3. PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns - COMPOSITE Key) have a unique identity which helps to find a particular record in a table more easily and quickly. A primary key column cannot contain NULL values.

Syntax:

1 2	CREATE TABLE <tableName> <columnName> <type> NOT NULL PRIMARY KEY

4. FOREIGN KEY: A FOREIGN KEY in one table points to a PRIMARY KEY in another table. FOREIGN KEY ensure the referential integrity of the data in one table to match values in another table.

Syntax:

1 2 3 4 5 6

CREATE TABLE <tableName1> ( <columnName> <type> NOT NULL PRIMARY KEY, <columnName> <type> NOT NULL, <columnName> <type> FOREIGN KEY REFERENCES <tableName2>(<columnName>) )

5. CHECK: Ensures that the value in a column meets a specific condition. The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Syntax:

1 2	CREATE TABLE <tableName> <columnName> int NOT NULL CHECK (<columnName>>0)

6. DEFAULT: Specifies a default value for a column. The default value will be added to all new records, if no other value is specified.

Syntax:

1 2	CREATE TABLE <tableName> <columnName> <type> DEFAULT <'defaultValue'>

Drop Constraint:

Syntax:

//where the constraint has been named

1 2	ALTER TABLE <tableName> DROP CONSTRAINT <constraintName>

//where the constraint has not been named

1 2	ALTER TABLE <tableName> ALTER COLUMN <columnName> DROP <constraint>