DDL - Part 4 - Column Level Constraints

Column Level Constraints:

ANSI permits constraints to be placed at column level.

Constraints can be named or unnamed. Using named constraints allows us to get HELP on the constraints.

4 types of column level attributes are:
 

PRIMARY KEY:	Used to guarantee uniqueness. The column should be defined as NOT NULL.
UNIQUE :	Used to guarantee uniqueness of a column. The column should be defined as NOT NULL.
CHECK :	Allows us to specify the range of value or value constraints permissible in the column.
REFERENCES:	Requires values to be reference checked before being allowed in the column.

Example 1:

Following query will fail because for making a column PRIMARY KEY it has to be defined as NOT NUL

CREATE TABLE EDW_RESTORE_TABLE.TEST

(

column1 integer PRIMARY KEY

);

Error: CREATE TABLE Failed. 3706:  Syntax error: PRIMARY KEY Column(s) must be NOT NULL. 

Correct DDL would be as follows:

CREATE TABLE MY_TEST_TABLES.TEST

(

column1 integer NOT NULL PRIMARY KEY

);

Note that here the constraint is not named. We should make a practice to add names to constraints.

DATABASE MY_TEST_TABLES

CREATE TABLE TEST

(

column1 integer NOT NULL CONSTRAINT PK_1 PRIMARY KEY

);

Above we specified a constraint at column level. However internally it always gets implemented at table level.

We can confirm this by doing a SHOW TABLE command.

Output of SHOW table:

show table test

CREATE SET TABLE MY_TEST_TABLES.test ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      column1 INTEGER NOT NULL,

CONSTRAINT PK_1 PRIMARY KEY ( column1 ))

;

Example 2:

CREATE TABLE MY_TEST_TABLES.TEST2

(

--All the constraints are named.

--To name a constraint we use the keywword CONSTRAINT followed by the name we provide

--followed by the actual cnstraint definition

column1 integer NOT NULL CONSTRAINT pk_2 PRIMARY KEY,

column2 integer NOT NULL CONSTRAINT Uk_2 UNIQUE,

--note that PRIMARY KE AND UNIQUE bot need columns to be NOT NULL

column3 integer CONSTRAINT ch2 CHECK(column3 > 100),

column4 integer constraint fk_1 references test1(column1)

);

SHOW TABLE TEST2;

CREATE SET TABLE MY_TEST_TABLES.TEST2 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      column1 INTEGER NOT NULL,

      column2 INTEGER NOT NULL,

      column3 INTEGER,

      column4 INTEGER,

      CONSTRAINT ch2 CHECK ( column3 >  100  ),

      CONSTRAINT pk_2 PRIMARY KEY ( column1 ),

      CONSTRAINT Uk_2 UNIQUE ( column2 ),

       CONSTRAINT fk_1 FOREIGN KEY ( column4 ) REFERENCES MY_TEST_TABLES.TEST1 ( COLUMN1 ))

;

All constraints get implemented at table level.

HELP INDEX MY_TEST_TABLES.TEST2

Unique?	Primary or Secondary?	Column Names	Index Id	Approximate Count	Index Name	Ordered or Partitioned?
Y	P	Column1	1	0.00	pk_2	H
Y	S	Column2	4	0.00	Uk_2	H

Following are the points to note:

1.    The PRIMARY KEY is implemented as unique primary index.
 

2.    The UNIQUE constraint is implemented as a unique secondary index.
 

3.    The CHECK constraint is implemented at the table level.
 

4.    The REFERENCES constraint is implemented as a FOREIGN KEY at the table level.
 

5.    Because the table is created in Teradata mode, it is a SET table.
 

6.    The character field is implemented with a NOT CASESPECIFIC attribute.