Re: Preferred method in creating primary key

Richard Foote wrote:

> 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

Not at all. I am suggesting that this is exactly what will happen and that is why it makes no sense to defer one.

In the foreign key constraint scenario ... once all commits are completed you get valid data. With primary key and unique constraints you get a rollback.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)