-
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
- CREATE TABLE table_name
- (
- col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
- col2 datatype
- );
Primary key constraint at table level
- ALTER TABLE table_name
- 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
- CREATE TABLE table_name
- (
- col1 datatype [CONSTRAINT constraint_name] UNIQUE,
- col2 datatype
- );
Unique key constraint at table level
- ALTER TABLE table_name
- 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
- CREATE TABLE table_name
- (
- col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
- col2 datatype
- );
Foreign key constraint at table level
- ALTER TABLE table_name
- 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
- CREATE TABLE table_name
- (
- col1 datatype [CONSTRAINT constraint_name] NOT NULL,
- col2 datatype
- );
Not Null constraint at table level
- ALTER TABLE table_name
- 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
- CREATE TABLE table_name
- (
- col1 datatype [CONSTRAINT constraint_name] CHECK (condition),
- col2 datatype
- );
- Check constraint at table level
- ALTER TABLE table_name ADD CONSTRAINTÂ constraint_name CHECK(condition)