DDL - Part 5 - Table Level Constraints

TABLE level constraints:

Earlier we saw column level constraints. We can also specify  constraints at table level .

Table level constraints are specified after the column definitions.

Like column level constraints they can also be named or unnamed.

Major difference between column level and table level constraints is that with column level constraints we cannot specify multi column constraints. .ie we cannot define a PK that comprises of multiple columns  using column level constraints. Here we need to use table level constraints as shown below:

CREATE TABLE tbl1
        (col1        INT   NOT NULL,
        col2        INT   NOT NULL,
        col3        INT   NOT NULL,
        col4        INT   NOT NULL,
        col5        INT,
        col6        INT,
CONSTRAINT  primary_1
        PRIMARY KEY       (col1,col2),
CONSTRAINT  unique_1   
        UNIQUE            (col3,col4),
CONSTRAINT  check_1     
        CHECK             (col2 > 0 OR col4 > 0),
CONSTRAINT  refer_1      
        FOREIGN KEY       (col5,col6)
       REFERENCES parent_1 (colA,colB)
        );

Note that we don’t use the word FOREIGN KEY while defining constraint at column level. Here we use it to identify which columns form the FOREIGN KEY.

Note following rules while creating table relations using REFERENCES.

1.    Table that references. .i.e. one with FOREIGN KEY is the child table.
 

2.    Table that is referenced is the parent table.
 

3.    Up to 64 foreign keys may be defined for a child table.
 

4.    Up to 64 references may reference a parent table.
 

5.    The table creator must have the REFERENCES privilege on referenced table.

6.    The referenced column must be defined as NOT NULL.
 

7.    The referenced column must be defined unique using one of the following methods:
 

·   USI

·   UPI

·   PRIMARY KEY constraint

·   UNIQUE constraint

(note that referenced column need not always be a PRIMARY KEY. Basic requirement is that the column should be NOT NULL and UNIQUE.)

Example:

CREATE TABLE MY_TEST_TABLES.TEST

(

column1 integer

);

IN table TEST the column column1 is not defined as NOT NULL and is not made unique. Now If we try to create a below table if will error out.

CREATE TABLE MY_TEST_TABLES.TEST2

(

column1 integer NOT NULL CONSTRAINT pk_2 PRIMARY KEY,

column4 integer constraint fk_1 references test(column1)

);

Error: CREATE TABLE Failed. 3977:  The Parent Key is not unique or not valid.

The query fails because column1 on test table is not defined as NOT NULL and UNIQUE.