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:
- Netezza Create Table Command and Examples
- Netezza Unique Key Constraint and Syntax
- Netezza Foreign Key Constraint and Syntax
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