Comparison of unique constraints and unique indexes in DB2 and Oracle

Comparison of unique constraints and unique indexes in DB2 and Oracle: db2 => connect to sample database connection information database server = db2/NT 10.1.0 SQL authorization id = ADMINIST... local Database alias = sample create table db2admin. test. The field id is null. The following process shows that the unique constraint in db2 does not allow empty column values. Db2 => the create table db2admin. test (id int) DB20000I SQL command is successfully completed. Db2 => alter table db2admin. test add constraint unique_test_id unique (id) DB21034E this command is processed as an SQL statement because it is an invalid "command line processor" command. During SQL processing, it returns: SQL0542N column named "ID" cannot be a primary key or a column with a unique key constraint, because it can contain null values. SQLSTATE = 42831 db2 => alter table db2admin. test alter column id set not null DB20000I SQL command is successfully completed. Www.2cto.com db2 => alter table db2admin. test add constraint unique_test_id unique (id) DB21034E this command is processed as an SQL statement because it is an invalid "command line processor" command. During SQL processing, it returns SQL0668N, which does not allow operations on the table "DB2ADMIN. TEST". The cause code is "7 ". SQLSTATE = 57016 db2 => reorg table db2admin. test DB20000I REORG command is successfully completed. Db2 => alter table db2admin. test add constraint unique_test_id unique (id) DB20000I SQL command is successfully completed. DB2 creates a unique index. The following process shows that the unique index allows the field to be null, but only one null value is allowed. Db2 => the drop table db2admin. test DB20000I SQL command is successfully completed. Db2 => the create table db2admin. test (id int) DB20000I SQL command is successfully completed. Db2 => create unique index idx_test_id on db2admin. test (id) DB20000I SQL command is successfully completed. Db2 => the insert into db2admin. test values (null) DB20000I SQL command is successfully completed. Db2 => insert into db2admin. test values (null) DB21034E this command is processed as an SQL statement because it is an invalid "command line processor" command. During SQL processing, it returns one or more values in the SQL0803N INSERT statement, UPDATE statement, or foreign key UPDATE caused by the DELETE statement, which is invalid, because the primary key, unique constraint, or unique index identified by "1" causes the table "DB2ADMIN. the index key of TEST cannot have duplicate values. SQLSTATE = 23505 unique constraints in Oracle: from the test process below, the unique constraints in Oracle allow fields to be empty, and Oracle considers null values to be different. Www.2cto.com SQL> create table test (id number); the table has been created. SQL> alter table test add constraint unique_test_id unique (id); the table has been changed. SQL> insert into test values (null); 1 row has been created. SQL> insert into test values (null); 1 row has been created. SQL> commit; submitted completely. Unique Oracle index: from the test process below, the unique constraint in Oracle also allows the field to be null, and Oracle considers the null value to be different from each other. SQL> drop table test; the table has been deleted. SQL> create table test (id number); the table has been created. SQL> create unique index idx_test_id on test (id); the index has been created. SQL> insert into test values (null); 1 row has been created. SQL> insert into test values (null); 1 row has been created. SQL> commit; submitted completely. In summary, the difference between the uniqueness constraint and the unique index in Oracle and DB2: the unique index of the database is the unique constraint. Oracle allows multiple null values to allow multiple null values. DB2 does not allow null values to allow up to one null value.