Re: Preferred method in creating primary key

Steve Bell wrote:

> Brian,
>
> I've read many of your posts here over the years, and you too Daniel; I respect you
> both..
>
> I must say, in my opinion, although I've been tempted to often create constraints
> as defferrable initially immediate, I've never done so...I recognize the theoretical
>
> benefits, but in a world of legacy systems, the lack of documentation, and the
> turnover
> that occurs in the IT world..I tend to go with Brian's opinion..notwithstanding
> Daniel, your
> completely valid point with respect to foreign keys, I still fret that we can't
> count on the
> application to use the database correctly and developers to write the code to handle
>
> deferrable constraints..
>
> In my designs that I send to production, the PK is the PK..no delay; however, my
> favorite
> tool is still SQL*Plus so maybe I'm a bit of a dinosaur..
>
> As such, I tend to write all of my constraints as immediate and let the analysts
> cope :)
>
> As Brian said, its only my opinion.
>
> Daniel Morgan wrote:
>
> > Brian Peasland wrote:
> >
> > > It makes some sense that this index is not unique since the constraint
> > > checking is deferred until you commit. Before the commit, you are
> > > allowed to insert all the duplicate key values you want, but you must
> > > remove them before you commit. If the index were UNIQUE, then the
> > > duplicate key values would not be allowed.
> > >
> > > Personally, I've hated deferrable constraint checking since it was
> > > introduced. We put constraints in the database so that the business
> > > rules on the data are adhered to. This is especially important since we
> > > don't trust the application to validate those business rules for us. So
> > > why do we decide to throw out those business rules "temporarily"? It
> > > just seemed to me to be a short cut for doing things right the first
> > > time. Maybe it's just my opinion though...
> > >
> > > Cheers,
> > > Brian
> > >
> > > <snipped>
> >
> > I agree with respect to primary key and unique constraints. They make no logical
> > sense. But they are a logical extension when applied to Foreign Keys.
> >
> > --
> > 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)

Deferred constraints can not be violated. The only thing that happens when a constraint is deferred is that the validity checking waits until the commit.

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.

--
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)