On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote:
>
> On Thu, 7 Nov 2003, Grant McLean wrote:
>
> > So it would seem that if I include the clauses:
> >
> > on delete restrict on update restrict
> >
> > Then the 'deferrable' which follows is only applied to creates and
> > not to updates or deletes.
> >
> > Since 'restrict' is the default, the clauses aren't adding any value
> > and can be omitted. In my case, the SQL is generated for me by
> > PowerDesigner. My workaround is to tweak the PowerDesigner output
> > definition to not include this line.
> >
> > I have seen this behaviour in both 7.2 and 7.3. Is it a bug? Or
> > am I misunderstanding something?
>
> Restrict is not the default, there is a difference between restrict and no
> action. In fact I believe the main point of restrict (which IIRC was added
> for sql99) is to allow you to have a deferred constraint that can do
> immediate checking of validity on pk changes.
I was basing my reasoning on the CREATE TABLE documentation which says:
NO ACTION
Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is
thedefault action.
RESTRICT
Same as NO ACTION.
So as you pointed out, RESTRICT is not the default, but according to the
docs NO ACTION is the default and RESTRICT is the same as NO ACTION.
Is the difference between the two documented anywhere?
Regards
Grant