Netezza Primary Key Constraint and Syntax

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:2 mins read

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

Read:

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

Netezza Primary Key Constraint Syntax and Example

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

create table PrimaryKey_demo ( col1 smallint NOT NULL PRIMARY KEY ,col2 date ,col3 varchar(60 ) ) Distribute on (col1); Output:NOTICE: primary key constraints not enforced CREATE TABLE

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

Insert duplicate value to created table:

SYSTEM.ADMIN(ADMIN)=> insert into PrimaryKey_demo values (1,now(), 'test1'); --This 1st value
INSERT 0 1 
SYSTEM.ADMIN(ADMIN)=> insert into PrimaryKey_demo values (1,now(), 'test1'); -- Duplicate value
INSERT 0 1 
SYSTEM.ADMIN(ADMIN)=>

Netezza Primary Key Constraint Alternate Syntax

create table PrimaryKey_demo1 (
 col1 smallint NOT NULL
 ,col2 date
 ,col3 varchar(30 )
 , CONSTRAINT PK_COL1 PRIMARY KEY (col1)
) distribute on (col1);

Output:
NOTICE: primary key constraints not enforced
CREATE TABLE

Alter Table to add Primary Key

SYSTEM.ADMIN(ADMIN)=> ALTER TABLE PrimaryKey_demo1 ADD CONSTRAINT PK_COL1 PRIMARY KEY (col1); 
NOTICE: primary key constraints not enforced 
ALTER TABLE
Tags: , , ,