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.
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
No comments:
Post a Comment