Example: Adding a Foreign Key to a Column-Partitioned Table - Advanced SQL Engine - Teradata Database

Suppose that after defining tables p and c, you decide to add a foreign key relationship between these tables, with column-partitioned table c referencing table p using a table-level foreign key constraint.

Table c has the following definition.

     CREATE TABLE c (
       c1  INTEGER, 
       cb  INTEGER, 
       cd1 DATE, 
       cc2 CHARACTER(30))
     PARTITION BY (COLUMN,
                   RANGE_N(cd1 BETWEEN DATE '2006-01-01' 
                               AND     DATE '2020-12-31'
                               EACH INTERVAL '1' MONTH));

Table p has the following definition.

     CREATE TABLE p (
       p1  INTEGER, 
       pb  INTEGER NOT NULL UNIQUE, 
       pc1 CHARACTER(10))
     PRIMARY INDEX (p1);

You submit the following ALTER TABLE statement to add the desired table-level foreign key constraint to table c, with column c.cb referencing column p.pb.

     ALTER TABLE c ADD FOREIGN KEY (cb) REFERENCES p (pb);

Because this foreign key only defines a relationship between a single column in the referencing and the referenced tables, you could just as well have defined it as a column-level constraint like the following.

     ALTER TABLE c ADD cb REFERENCES p (pb);

These ALTER TABLE statements both produce the following error table definition.

     CREATE TABLE c_0 (
       c1  INTEGER, 
       cb  INTEGER, 
       cd1 DATE, 
       cd2 CHARACTER(30))
     NO PRIMARY INDEX;