SQL Integrity Constraints or Constraints

Primary Key Constraints

Primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It can not accept null, duplicate values.

Primary key constraint at column level

1. CREATE TABLE table_name
2. (
3. col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
4. col2 datatype
5. );

Primary key constraint at table level

1. ALTER TABLE table_name
2. ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)

Unique Key Constraints

Unique key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table.It is like Primary key but it can accept only one null value and it can not have duplicate values

Unique key constraint at column level

1. CREATE TABLE table_name
2. (
3. col1 datatype [CONSTRAINT constraint_name] UNIQUE,
4. col2 datatype
5. );

Unique key constraint at table level

1. ALTER TABLE table_name
2. ADD[CONSTRAINT constraint_name] UNIQUE (col1,col2)

Foreign Key Constraints

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

Foreign key constraint at column level

1. CREATE TABLE table_name
2. (
3. col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
4. col2 datatype
5. );

Foreign key constraint at table level

1. ALTER TABLE table_name
2. ADD[CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_col)

Not Null Constraints

This constraint ensures that all rows in the database table must contain value for the column which is specified as not null means a null value is not allowed in that column.

Not Null constraint at column level

1. CREATE TABLE table_name
2. (
3. col1 datatype [CONSTRAINT constraint_name] NOT NULL,
4. col2 datatype
5. );

Not Null constraint at table level

1. ALTER TABLE table_name
2. ALTER COLUMN col1 datatype NOT NULL

Check Constraints

This constraint defines a business rule on a column in the database table that each row of the table must follow this rule.

Check constraint at column level

1. ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition)