Netezza Unique Key Constraint and Syntax

Netezza nzsql supports SQL-92 standard. Netezza data warehouse appliance supports referential integrity such as Netezza unique key, primary key, and foreign keys as part of SQL-92 standard requirement. You can create Netezza unique 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 Primary Key Constraint and Syntax
• Netezza Foreign Key Constraint and Syntax

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

Netezza Unique Key Constraint Syntax and Example

You can mention the Netezza Unique Key constraint when creating table in Netezza nzsql:

create table PrimaryKey_demo3 (
 col1 smallint Unique
 ,col3 varchar(300 )
) distribute on (col1);

Output:
NOTICE: unique key constraints not enforced 
CREATE TABLE

Now let us test if the Netezza unique key is enforced. You can insert duplicate values in Netezza table.

SYSTEM.ADMIN(ADMIN)=> insert into PrimaryKey_demo3 values (1,'test1'); 
INSERT 0 1 
SYSTEM.ADMIN(ADMIN)=> insert into PrimaryKey_demo3 values (1,'test1'); 
INSERT 0 1 
SYSTEM.ADMIN(ADMIN)=>

Alter Table to add Unique Key Constraint

SYSTEM.ADMIN(ADMIN)=> alter table PrimaryKey_demo3 add constraint uk_col1 unique (col1);