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