See related post:
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.
/*
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 !!! ---
--- 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 !!! ---
--- 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
No comments:
Post a Comment