Constraints in Teradata - With detailed example and work logs and Syntax

Constraints can be grouped as Column level and Table level constraints.

Column constraints apply to single columns as a part of the column definition. Column constraints include:

•CHECK constraint definition clause on a single column

•PRIMARY KEY constraint definition clause on a single column

•REFERENCES constraint definition clause on a single column

•UNIQUE constraint definition clause

Table constraints apply to multiple columns. Table-level constraints include:

•CHECK constraint definition clause on multiple columns

•REFERENCES constraint definition clause on multiple columns

•PRIMARY KEY constraint definition clause on multiple columns

•UNIQUE constraint definition clause on multiple columns

•FOREIGN KEY constraint definition clause

FOREIGN KEY constraint definitions must also specify a REFERENCES clause.

Please go through the below work log to understand more about constraints in Teradata.

/* CONSTRAINTS IN TERADATA */

database financial;

/* The base table should exists and a primary key should be defined already */

/* Only then Foreign key/referential integrity creation will be allowed */

create table custonline_constraint_1

(

cust_id integer not null primary key

) ;

/* Add the integrity constraints on the child table */

drop table constraint_test;

create multiset table constraint_test

(

cust1_id integer not null unique check (cust1_id > 0)

, cust_name varchar(50)

, c_id integer references custonline_constraint_1(cust_id)

,check (char_length(cust_name) > 1)

)

primary index(cust1_id, cust_name);

/* The below insert will give "CHECK CONSTRAINT VIOLATION" as the value we are inserting for cust1_id is 0

 * The check constraint allows value which are greater than zero

 */

insert into constraint_test values(0,'prabhu', 11);

/* we will need to have the foreign key value for c_id column in the parent table (custonline_constraint_1)

 * if the value doesnt exists, we will get an error "REFERENTAIL CONSTRAINT VIOLATION"

 */

insert into constraint_test values(1,'prabhu', 11);

/* Let us now check the with check option of views

 * If any row is inserted in the table which will not be displayed in the view, then those rows are discarded

 * STEP-1 will be to insert records in the base table custonline_constraint_1

*   STEP-2 will insert the FOREIGN KEY into the table constraint_test

 */

insert into custonline_constraint_1 values(11);

insert into constraint_test values(1,'prabhu', 11);

/* Create the view with check option

 * Then try to insert rows having cust_id > 10 using the view

 */

replace view v_constraint_test as select * from constraint_test where cust1_id < 10 with check option;

insert into v_constraint_test values(13,'prabhu', 11);

/* If you insert rows directly into the table, the rows with cust1_id > 10 will be inserted

 * The check constraint only restricsts inserts or updates using the view

 */

insert into constraint_test values(13,'prabhu', 11);

/* The below insert statement fails as there is check constraint that requires values of cust_name

 * to have length larger than one character

 */

insert into constraint_test values(12,'A', 11);

--- successfully inserts 1 row into the table

select * from v_constraint_test;

Please go through the above queries to understand each concept. 

The next blog will contain a few additional implementation of constraints.