The format is:

          [CONSTRAINT table_constraint_name]
            CHECK (condition)

The CHECK can be defined as a column constraint:

          CREATE TABLE      EMPLOYEE_TBL (
                 ...
                 SEX        CHAR(1)   CHECK (IN ('M', 'F')),
                 ... );

The CHECK can be defined as a named table constraint:

          CREATE TABLE EMPLOYEE_TBL (
                 ...
                 SEX        CHAR(1)    NOT NULL,
                 ...
                 CONSTRAINT SEX_CHK
                      CHECK (SEX IN ('M', 'F')),
                 ... );

                                            or

          CREATE TABLE EMPLOYEE_TBL (
                 ...
                 BADGE      CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT BADGE_CHK
                      CHECK (
                      (SUBSTRING(BADGE FROM 1 FOR 1) = 'E')
                      AND
                      (CAST(SUBSTRING(BADGE FROM 2 FOR 3)
                      AS INTEGER(BETWEEN 0 AND 999))
                 ...        );

 

> If the CHECK condition is violated, the INSERT or UPDATE will not happen and an error condition will be returned.

> The SQL standard says that CHECK can not be used with CURRENT_DATETIME and CURRENT_USER.
Access, Postgre SQL, and SQL Server can CHECK these values.

> MySQL only handles CHECK constraints, but it can not handle named CHECK constraints.

A CHECK constraint makes sure that a column or columns have limited values.

> A table can have zero, one, or many CHECK constraints. It uses the reserved word CHECK in the CREATE TABLE statement.

> A simple constraint (one column) can be defined as a column constraint or a table constraint.
As a simple column constraint, CHECK is placed in the column definition.

> A composite constraint (multiple columns) can only be defines as a table constraint.
As a table constraint, the CHECK definition has the column names specified.

> A CHECK column constraint works just like a WHERE clause with BETWEEN, IN, IS NULL, or LIKE.
It can allow only specific values, minimum or maximum values, or a range of values.
If you apply a CHECK constraint after the table has been populated with rows, you must ensure that the values in the table fall within the CHECK constraint values.
If the CHECK constraint is in place before the table is populated, the constraint will ensure proper values.

> General practice is to use a CONSTRAINT clause to explicitly name all constraints.
This gives a constraint name to make maintenance easier.

The format is:

          [CONSTRAINT table_constraint_name]
            UNIQUE (unique_columns)

The UNIQUE can be defined as a column constraint:

          CREATE TABLE      EMPLOYEE_TBL (
                 ...
                 BADGE_ID   CHAR(4)   NOT NULL UNIQUE,
                 ...        );

The UNIQUE can be defined as a named table constraint:

          CREATE TABLE EMPLOYEE_TBL (
                 ...
                 BADGE_ID   CHAR(4)   NOT NULL,
                 ...
                 CONSTRAINT EMPLOYEE_UNQ1
                     UNIQUE (BADGE_ID),
                 ... );

                                               or

          CREATE TABLE      SAMPLE_TBL (
                 SAMPLE1    CHAR(4)   NOT NULL,
                 SAMPLE2    CHAR(4)   NOT NULL,
                 ...
                 CONSTRAINT SAMPLE_UNQ2
                     UNIQUE (SAMPLE1, SAMPLE2)
                 ... );

The UNIQUE constraint makes sure that a column or columns have no duplicate values.> A table can have zero, one, or many UNIQUE constraints. It uses the reserved word UNIQUE in the CREATE TABLE statement.

> A simple constraint (one column) can be defined as a column constraint or a table constraint. As a simple column constraint, UNIQUE is placed in the column definition.

> A composite constraint (multiple columns) can only be defined as a table constraint. As a table constraint, the UNIQUE definition has the column names specified.

> A UNIQUE column can forbid nulls with NOT NULL.

> General practice is to use a CONSTRAINT clause to explicitly name all constraints. This gives a constraint name to make maintenance easier.

You can define the actions performed by SQL when you UPDATE or DELETE a key value in the parent table. This ON UPDATE or ON DELETE clause is placed in the FOREIGN KEY constraint.

          [CONSTRAINT  column_constraint_name]
            REFERENCES parent_table[(parent_columns)]
            [ON UPDATE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
            [ON DELETE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]

                                                                              or

          [CONSTRAINT  table_constraint_name]
            FOREIGN KEY (key_columns)
            REFERENCES parent_table[(parent_columns)]
            [ON UPDATE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
            [ON DELETE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]

ON UPDATE:

NO ACTION   Generates an error on a foreign key violation. This is the default.

CASCADE     Updates the dependent foreign key value to the parent key value.

RESTRICT    Does not allow the dependent foreign key to change value.

SET NULL    Sets the dependent foreign key values to null.

SET DEFAULT Sets the dependent foreign key values to their default values.

ON DELETE:

NO ACTION   Generates an error on a foreign key violation. This is the default.

CASCADE     Deletes the rows that contain foreign key values that match the deleted parent key value.

RESTRICT    Does not allow the dependent foreign key to change value.

SET NULL    Sets the dependent foreign key values to null.

SET DEFAULT Sets the dependent foreign key values to their default values.

The format is:

          [CONSTRAINT  column_constraint_name]
            REFERENCES parent_table[(parent_columns)]

or

          [CONSTRAINT    table_constraint_name]
            FOREIGN KEY  (key_columns)
              REFERENCES parent_table[(parent_columns)]

You can omit the parent_column if the parent_column is the primary key of the parent_table. However, this is not recommended.
The foreign key can be defined as a column constraint:

          CREATE TABLE     PAYROLL_TBL (
                 EMPL_ID   CHAR(4) NOT NULL
                           REFERENCES EMPLOYEE_TBL(EMPL_ID),
                 ...       );

The foreign key can be defined as a named table constraint:

          CREATE TABLE PAYROLL_TBL (
                 EMPL_ID        CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT     PAYROLL_FK1
                    FOREIGN KEY (EMPL_ID)
                     REFERENCES EMPLOYEE_TBL(EMPL_ID),
                 ...            );

                                                            or

          CREATE TABLE SAMPLE_TBL (
                 SAMPLE1_ID     CHAR(4) NOT NULL,
                 SAMPLE2_ID     CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT     SAMPLE_FK
                    FOREIGN KEY (SAMPLE1_ID, SAMPLE2_ID)
                     REFERENCES PARENT_TBL(PARENT1_ID, PARENT2_ID),
                 ...            );

Here is how a foreign key works (so that orphan rows can not happen):
When inserting a new row in the foreign key table:
The DBMS system makes sure that the new FOREIGN KEY value matches a UNIQUE or PRIMARY KEY value in the parent table. If there is no match, the new row in the foreign key table will not be inserted.

When updating an existing row in the foreign key table:
The DBMS system makes sure that the updated FOREIGN KEY value matches a UNIQUE or PRIMARY KEY value in the parent table. If there is no match, the existing row in the foreign key table will not be updated.

When deleting an existing row in the foreign key table:
There is no need for a referential integrity check.
When inserting a new row in the parent table:
There is no need for a referential integrity check.

When updating an existing row in the parent table:
The DBMS system makes sure that none of the FOREIGN KEY values match a UNIQUE or PRIMARY KEY value in the child table. If a match exists, the existing row in the parent key table will not be updated. However, if ON UPDATE CASCADE is specified, the parent row and all matching foreign key rows will be updated with the new key values.

When deleting an existing row in the parent table:
The DBMS system makes sure that none of the FOREIGN KEY values match a UNIQUE or PRIMARY KEY value in the child table. If a match exists, the existing row in the parent key table will not be deleted. However, if ON UPDATE CASCADE is specified, the parent row and all matching foreign key rows will be deleted.

A foreign key has the following features:> A table can have zero, one, or many foreign keys. It uses the reserved words REFERENCE or FOREIGN KEY in the CREATE TABLE statement.

> A simple key (one column) can be defined as a column constraint or a table constraint. As a simple key column constraint, REFERENCES is placed in the column definition.

> A composite key (multiple columns) can only be defined as a table constraint. As a table constraint, the REFERENCES and PRIMARY KEY definitions have the column names specified.

> A FOREIGN KEY column can reference a PRIMARY KEY or a UNIQUE column in another table, and this references column is called the parent key. The FOREIGN KEY and parent key columns must be of the same or convertibly similar data type.

> Foreign key constraints make updates and deletes easier, because inconsistencies are not introduced into the database. For example, you can not place an employee into the payroll table, until that employee exists in the employee table, and when you delete the employee from the employee table, the employee will also be deleted from the payroll table. This prevents orphan rows.

> The general practice is to use a CONSTRAINT clause to explicitly name all foreign keys. This gives a foreign key constraint name to make maintenance easier.

Foreign keys were discussed in Relational Database Basics. Let us review:Foreign keys are used to associate data between tables. For instance, a foreign key of REP in the CUSTOMER_TBL points to the primary key EMPL_ID in the EMPLOYEE_TBL.

> A foreign key is one or more columns in a table whose values reference values in another table.

> A foreign key in the child table establishes a relationship to the primary key in the parent table.

> The matching foreign key and primary key can have different names.

> If the relationship is enforced, then this is called referential integrity.

> If the relationship is not enforced and there is a foreign key in a child table that does not match the primary key of the parent table, then this is called an orphan row.

> Unlike primary keys, foreign keys can be empty with a null.

> Foreign keys are not always unique in their table.

> A foreign key can reference the primary key of the same table. For instance, the MANAGER of an employee can reference the EMPLOYEE ID for the manager.

 

The format is:

          [CONSTRAINT column_constraint_name]
            PRIMARY KEY

                                                  or

          [CONSTRAINT table_constraint_name]
            PRIMARY KEY (key_columns)

The primary key can be defined as a column constraint:

          CREATE TABLE     SAMPLE_TBL (
                 SAMPLE_ID CHAR(12)    NOT NULL PRIMARY KEY,
                 ...       );

The primary key can be defined as an unnamed table constraint:

          CREATE TABLE       SAMPLE_TBL (
                 SAMPLE_ID   CHAR(12)    NOT NULL,
                 ...
                 PRIMARY KEY (SAMPLE_ID)
                 ...         );

                                                  or

          CREATE TABLE       SAMPLE_TBL (
                 SAMPLE1_ID  CHAR(12)    NOT NULL,
                 SAMPLE2_ID  CHAR(12)    NOT NULL,
                 ...
                 PRIMARY KEY (SAMPLE1_ID, SAMPLE2_ID)
                 ...         );

The primary key can be defined as a named table constraint:

          CREATE TABLE       SAMPLE_TBL (
                 SAMPLE_ID   CHAR(12)    NOT NULL,
                 ...
                 CONSTRAINT  SAMPLE_PK
                 PRIMARY KEY (SAMPLE_ID)
                 ...         );

                                                  or

          CREATE TABLE SAMPLE_TBL (
                 SAMPLE1_ID  CHAR(12)    NOT NULL,
                 SAMPLE2_ID  CHAR(12)    NOT NULL,
                 ...
                 CONSTRAINT  SAMPLE_PK
                 PRIMARY KEY (SAMPLE1_ID, SAMPLE2_ID)
                 ...         );