Re: Preferred method in creating primary key

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F3C19C6.E5665C80_at_exxesolutions.com>...
>
> This can be of great value in loading tables where referential constraints exist and
> where it is time consuming or difficult to track down all of the various relationships
> between parents and children. You can insert all of the records in any order and then
> commit them.
>
> Deferring a primary key or unique constraint is quite a different matter. If it can't
> handle to constraint upon initial insertion ... it never will.

Hi Daniel,

Are you suggesting the following is not possible ...

SQL> create table test_bowie as select * from dba_tables;

Table created.

SQL> alter table test_bowie add primary key (owner, table_name) deferrable initi ally deferred;

Table altered.

SQL> insert into test_bowie (owner, table_name) values ('BOWIE', 'DUPLICATE');

1 row created.

SQL> / 1 row created.

SQL> / 1 row created.

SQL> / 1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYS.SYS_C003101) violated

The beauty of deferrable constraints...

Perfectly valid with PK or Unique constraints since Oracle8.

Cheers

Richard Received on Fri Aug 15 2003 - 00:04:30 CDT