Netezza SQL: UNIQUE constraint clause

See related post: 

• Netezza SQL: PRIMARY KEY clause 
• Netezza SQL: FOREIGN KEY  clause

Netezza SQL compiles with ANSI SQL-92 standard implementing Postgrees SQL. The ANSI SQL-92 standard supported by most relational databases on the market. 

Netezza supports referential integrity (e.g. definitions for foreign, primary, and unique keys) as part of SQL-92 standard requirement. However, the keys are not enforced. This means that you can define the keys in SQL but they won't make any affect on the data. Nothing would prevent to violate referential integrity like inserting non matching values in child table that suppose to match to parent table. 

Let's experiment with UNIQUE KEY constraint:

/* Start time 10/15/2013 6:15:05 PM, end time 10/15/2013 6:15:05 PM. */

/* Duration 0.0270027 sec. */

create table test2delete (

        col1 smallint

       ,col3 varchar(30 )

);

/*

      NOTICE:  unique key constraints not enforced  <-- Netezza complains, but proceeds.

 */

/* Start time 10/15/2013 6:15:05 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.0370037 sec. */

alter table test2delete add constraint uk_test2delete unique (col1);

/* Start time 10/15/2013 6:15:06 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.2890289 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,'mystring');

/* Start time 10/15/2013 6:15:06 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.0760076 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,'mystring');

/* Start time 10/15/2013 6:15:06 PM, end time 10/15/2013 6:15:06 PM. */

/* Duration 0.0770077 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,'mystring');

<-- All DUPLICATES inserted !!!!

---

To conclude, Netezza NPS 7.2 documentation emphasizes: 

Note: The system permits and maintains primary key, default, foreign key, unique, and references. Because IBM® Netezza® does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

Related documentation:   Column and table constraints

Netezza SQL: FOREIGN KEY clause

See related post: 

• Netezza SQL: PRIMARY KEY clause
• Netezza SQL: UNIQUE constraint clause

Netezza SQL compiles with ANSI SQL-92 standard implementing Postgrees SQL. The ANSI SQL-92 standard supported by most relational databases on the market. 

Netezza supports referential integrity (e.g. definitions for foreign, primary, and unique keys) as part of SQL-92 standard requirement. However, the keys are not enforced. This means that you can define the keys in SQL but they won't make any affect on the data. Nothing would prevent to violate referential integrity like inserting non matching values in child table that suppose to match to parent table. 

Let's experiment with FOREIGN KEY constraint:

------------------------------------------------------------------

/*

NOTICE:  foreign key constraints not enforced       <-- Netezza complains, but proceeds.

 */

/* Start time 4/25/2013 6:21:32 PM, end time 4/25/2013 6:21:32 PM. */

/* Duration 0.078125 sec. */

CREATE TABLE table2ref

(

        col1    smallint NOT NULL

       ,colref2 smallint

       ,col3      varchar(30 )

  ,CONSTRAINT fk_column_colref2          <-- THIS IS NAME OF REF CONSTRAINT

   FOREIGN KEY (colref2) 

   REFERENCES test2delete (col1)   <-- the ref table "test2delete" must exist. 

);                            if table "test2delete" does not exist SQL will fail.

/* Start time 4/25/2013 6:21:32 PM, end time 4/25/2013 6:21:37 PM. */

/* Duration 4.53125 sec. */                        

/* Records Affected: 1. */

insert into table2ref values (3,12345,'reftabstring');   <-- THIS IS NON-EXISTING KEY "12345"

                                  in table "test2delete.

                                  Nothing stops inserting non-matching value "12345"

---

To conclude, Netezza NPS 7.2 documentation emphasizes: 

Note: The system permits and maintains primary key, default, foreign key, unique, and references. Because IBM® Netezza® does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

Related documentation:   Column and table constraints

Netezza SQL: PRIMARY KEY clause

See related post:

• Netezza SQL: FOREIGN KEY  clause
• Netezza SQL: UNIQUE constraint clause

Netezza SQL compiles with ANSI SQL-92 standard implementing Postgrees SQL. The ANSI SQL-92 standard supported by most relational databases on the market. 

Netezza supports referential integrity (e.g. definitions for foreign, primary, and unique keys) as part of SQL-92 standard requirement. However, the keys are not enforced. This means that you can define the keys in SQL but they won't make any affect on the data. Nothing would prevent to violate referential integrity like inserting non matching values in child table that suppose to match to parent table. 

Let's experiment with PRIMARY KEY constraint:

/*

NOTICE:  primary key constraints not enforced  <-- Netezza SQL Parser complains here

 */

/* Start time 4/25/2013 5:36:27 PM, end time 4/25/2013 5:36:27 PM. */

/* Duration 0.0624992 sec. */

create table test2delete (

        col1 smallint NOT NULL

       ,col2 date

       ,col3 varchar(30 )

       , CONSTRAINT PK_COL1 PRIMARY KEY (col1)  <-- THIS IS CONSTRAINT

);

/* Start time 4/25/2013 5:36:27 PM, end time 4/25/2013 5:36:28 PM. */

/* Duration 0.4999936 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring'); <-- First value "1"

/* Start time 4/25/2013 5:36:28 PM, end time 4/25/2013 5:36:28 PM. */

/* Duration 0.4218696 sec. */ 

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring');  <-- Second value "1"
                                                         --- THIS IS DUPLICATE !!! ---

Note: observe how long singe records INSERT took: 0.42 sec and 0.49 sec. Netezza is very slow when it comes to a single record INSERT. 

---

Alternative syntax for PRIMARY KEY

/* Start time 4/25/2013 6:09:53 PM, end time 4/25/2013 6:09:53 PM. */

/* Duration 0.0312536 sec. */

create table test2delete (

        col1 smallint NOT NULL

       ,col2 date

       ,col3 varchar(30 )

);

/*

NOTICE:  primary key constraints not enforced   <-- Netezza complains here, but proceeds

 */

/* Start time 4/25/2013 6:09:53 PM, end time 4/25/2013 6:09:53 PM. */

/* Duration 0.0312536 sec. */

ALTER TABLE test2delete ADD CONSTRAINT PK_COL1 PRIMARY KEY (col1);  <-- THIS IS CONSTRAINT

/* Start time 4/25/2013 6:09:53 PM, end time 4/25/2013 6:11:27 PM. */

/* Duration 93.3857568 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring'); <-- First value "1"

/* Start time 4/25/2013 6:11:27 PM, end time 4/25/2013 6:11:50 PM. */

/* Duration 23.1589176 sec. */

/* Records Affected: 1. */ 

insert into test2delete values (1,current_date, 'mystring'); <-- Second value "1"
                                                         --- THIS IS DUPLICATE !!! ---

 <-- DUPLICATES IN THE TABLE  !!! 

Note: observe how long singe records INSERT took: 93 sec and 23 sec. Netezza is very slow when it comes to a single record INSERT. 

---

To conclude, Netezza NPS 7.2 documentation emphasizes: 

Note: The system permits and maintains primary key, default, foreign key, unique, and references. Because IBM® Netezza® does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

Related documentation:   Column and table constraints