Netezza Foreign Key Constraint and Syntax

Netezza nzsql supports SQL-92 standard. Netezza data warehouse appliance supports referential integrity such as Netezza foreign key, primary key, and unique keys as part of SQL-92 standard requirement. You can create Netezza foreign key constraint while creating tables in Netezza database but it will not be enforced while loading Netezza tables.

Read:

• Netezza nzsql command and its Usage
• Netezza Unique Key Constraint and Syntax
• Netezza Primary Key Constraint and Syntax

In this post we will learn about the Netezza foreign key constraints and its syntax.

Netezza Foreign Key Constraint Syntax and Example

You can mention the Foreign Key constraint by referring the parent table when creating table in Netezza nzsql:

CREATE TABLE PrimaryKey_demo2
(
 col1 smallint NOT NULL
 ,colref2 smallint 
 ,col3 varchar(300 )
 ,CONSTRAINT fk_column_colref2 
 FOREIGN KEY (colref2) 
 REFERENCES PrimaryKey_demo1 (col1) 
) distribute on (col1);

Output:
NOTICE: foreign key constraints not enforced 
CREATE TABLE

Now let us test if the Netezza foreign key is enforced. In Netezza, foreign key constraint is not enforced. You can insert values in Netezza table that are not present in the parent table.

The parent table contains:

SYSTEM.ADMIN(ADMIN)=> select * from PrimaryKey_demo1; 
 COL1 | COL2 | COL3 
------+------------+------- 
 1 | 2016-10-22 | test1 
 1 | 2016-10-22 | test1 
(2 rows)

SYSTEM.ADMIN(ADMIN)=> insert into PrimaryKey_demo2 values (3,123,'test2'); -- This data is not present in parent table
INSERT 0 1 
SYSTEM.ADMIN(ADMIN)=>

Alter Table to Add Foreign Key Constraint

TRAINING.ADMIN(ADMIN)=>
ALTER TABLE test_demo ADD CONSTRAINT test_fk FOREIGN KEY (col1)
REFERENCES test (col1);