Re: DEFERRABLE NOT NULL constraint

P=C3=A5 tirsdag 05. februar 2013 kl. 11:26:20, skrev Alban Hertroys &l=
t;haramrae@gmail.co=
m>:

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">On 5 February 2013 11:15, Andreas Josep=
h Krogh <<a href=3D"mailto:andreak@officenet.no" targe=
t=3D"_blank">andreak@officenet.no> wrote:


<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
P=C3=A5 tirsdag 05. februar 2013 kl. 11:04:27, skrev Thomas Kellerer &=
lt;spam_eater@gmx.n=
et>:


<blockquote style=3D"border-left:1px solid rgb(204,204,204);margin:0pt 0pt =
0pt 0.8ex;padding-left:1ex">
Andreas =
Joseph Krogh, 05.02.2013 10:57:
> The value of having NOT NULL deferrable is, well, to not check for
> NULL until the tx commits. When working with ORMs this often is the
> case, especially with circular FKs.

With circular FKs it's enough to define the FK constraint as deferred.


=C2=A0


I meant; circular FKs which are also NOT NULL=C2=A0


=C2=A0

=C2=A0If you would use that, every pair of circular inserts would requ=
ire 2 inserts and an update (=3Dinsert & delete in MVCC):

=C2=A0

1; insert node 1 with FK null,

2; insert node 2 referencing node1,

3; update node 1 with FK to node 2.

=C2=A0

OTOH, when you decide the FK from node 1 to node 2 before inserting no=
de 1 and have the FK constraint(s) deferrable, then you only need to insert=
 both records:

=C2=A0

1; decide FK key from node 1 to node 2,

2; insert node 1 referencing node 2,

3; insert node 2 referencing node 1

=C2=A0

This case typically only occurs when you're using surrogate keys, but =
even in that case you can select nextval(...).

=C2=A0

The deferred FK approach has the benefit that you don't create 3 copie=
s of the record for node 1, so table and index bloat will be less.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

=C2=A0

There are lots of things you can do, but when it's the ORM which does =
it you have limited control, and that's the way it should to be (me as appl=
ication-developer having to worry less about such details).

=C2=A0

--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 mob: =
+47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc

=C2=A0=