db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "RPost" <rp0...@pacbell.net>
Subject Re: DB2 mode vs. Cloudscape mode
Date Fri, 04 Mar 2005 21:20:00 GMT
> "Mike Matrigali" wrote:
> Does anyone know what oracle, microsoft and mysql do?

The Oracle 10g SQL Reference (page 8-12) states:

'To satisfy a unique constraint, no two rows in the table can have the same
value for the unique key. However, the unique key made up of a single column
can contain nulls. To satisfy a composite unique key, no two rows in the
table or view can have the same combination of values in the key columns.
Any row that contains nulls in all key columns automatically satisfies the
constraint. However, two rows that contain nulls for one or more key columns
and the same combination of values for the other key columns violate the
constraint.'

Testing shows this:

1. create table a (col1 number, col2 number);
2. create unique index a_ndx on a (col1, col2);
3. table accepts multiple rows with all null index columns
    a. insert into a values (null,null);
    b. insert into a values (null,null);
    c. insert into a values (null,null);
    d. insert into a values (null,null);
4. table does not accept multiple rows with only some null index columns if
non-null columns are duplicates
    a. insert into a values (1,null);
    b. insert into a values (1,null); - ORA-00001: unique constraint
(SYSTEM.A_NDX) violated


Mime
View raw message