Thread: check constraint
Hi,
I would like to know if it is possible to set "complex" constraints on
databases such as:
A->B means that in table A, each record references a record of table B
(or NULL)
so we have:
A->B
C->B
D->C
D->A
constraint: for one record of D, D->A->B = D->C->B
Hope it is clear enough.
Erwan
On Mon, Jun 02, 2003 at 10:52:00 +0200, erwan ancel <erwan.ancel@free.fr> wrote: > Hi, > I would like to know if it is possible to set "complex" constraints on > databases such as: > > A->B means that in table A, each record references a record of table B > (or NULL) > > so we have: > > A->B > C->B > D->C > D->A > constraint: for one record of D, D->A->B = D->C->B > > Hope it is clear enough. It looks like you are talking about foreign keys. Postgres has foreign key constraints. You can look at the create table documentation to see how to define them when creating a table.
well, no... these are not direct foreign keys. The constraint here is
that for a given record of D, B pointed by A pointed by the given D must
be the same as B pointed by C pointed by the given D.
This is not a foreign key, or foreign keys are much more than what I
thought.
Erwan
Le lun 02/06/2003 à 15:08, Bruno Wolff III a écrit :
> On Mon, Jun 02, 2003 at 10:52:00 +0200,
> erwan ancel <erwan.ancel@free.fr> wrote:
> > Hi,
> > I would like to know if it is possible to set "complex" constraints on
> > databases such as:
> >
> > A->B means that in table A, each record references a record of table B
> > (or NULL)
> >
> > so we have:
> >
> > A->B
> > C->B
> > D->C
> > D->A
> > constraint: for one record of D, D->A->B = D->C->B
> >
> > Hope it is clear enough.
>
> It looks like you are talking about foreign keys. Postgres has foreign key
> constraints. You can look at the create table documentation to see how
> to define them when creating a table.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
On Mon, Jun 02, 2003 at 16:00:43 +0200, erwan ancel <erwan.ancel@free.fr> wrote: > > well, no... these are not direct foreign keys. The constraint here is > that for a given record of D, B pointed by A pointed by the given D must > be the same as B pointed by C pointed by the given D. I believe one way to do this is with after triggers. Another way to do it is by storing the key for B in D. Then change the foreign key references into A and C to use the primary keys for A and C combined with the value of the primary key for B stored in D. Something like: create table B ( bkey serial primary key ); create table A ( akey serial primary key, bkey int references B ); create unique index aindex on A(akey,bkey); create table C ( ckey serial primary key, bkey int references B ); create unique index cindex on C(ckey,bkey); create table D ( dkey serial primary key, akey int, bkey int references B, ckey int, foreign key (akey, bkey) references A (akey, bkey), foreign key (ckey, bkey) references A (ckey, bkey), ); I haven't actually tested the above statements, so there might be some syntax errors or typos in them, but it should lay out the idea for you to use.
On Monday 02 Jun 2003 3:00 pm, erwan ancel wrote: > well, no... these are not direct foreign keys. The constraint here is > that for a given record of D, B pointed by A pointed by the given D must > be the same as B pointed by C pointed by the given D. > > This is not a foreign key, or foreign keys are much more than what I > thought. > Erwan Sounds like it might be the scenario described in: http://techdocs.postgresql.org/guides/BriefTriggerExample -- Richard Huxton
Ok, thanks. I just did it with 3 triggers. And it seems to work well. I wanted to avoid this, but it seems to be the best solution. Le jeu 05/06/2003 à 23:22, Jan Wieck a écrit : > erwan ancel wrote: > > well, no... these are not direct foreign keys. The constraint here is > > that for a given record of D, B pointed by A pointed by the given D must > > be the same as B pointed by C pointed by the given D. > > > > This is not a foreign key, or foreign keys are much more than what I > > thought. > > Looks like a foreign key around the corner to me. Regular referential > integrity does not allow you to define that, unless you include the B > keys referenced in A and C into separate fields in D and build multi > column foreign keys (probably with ON UPDATE CASCADE). > > The problem arising from that is that you need to know the values ahead > or do a lookup and put them into D in a BEFORE trigger. > > Also I see problems arising if you later want to update A and/or C. I > haven't thought it through completely, maybe defining the constraints > deferred can help you out of that. > > > Jan > > > > > Erwan > > > > Le lun 02/06/2003 à 15:08, Bruno Wolff III a écrit : > >> On Mon, Jun 02, 2003 at 10:52:00 +0200, > >> erwan ancel <erwan.ancel@free.fr> wrote: > >> > Hi, > >> > I would like to know if it is possible to set "complex" constraints on > >> > databases such as: > >> > > >> > A->B means that in table A, each record references a record of table B > >> > (or NULL) > >> > > >> > so we have: > >> > > >> > A->B > >> > C->B > >> > D->C > >> > D->A > >> > constraint: for one record of D, D->A->B = D->C->B > >> > > >> > Hope it is clear enough. > >> > >> It looks like you are talking about foreign keys. Postgres has foreign key > >> constraints. You can look at the create table documentation to see how > >> to define them when creating a table. > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >> > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > >
erwan ancel wrote: > well, no... these are not direct foreign keys. The constraint here is > that for a given record of D, B pointed by A pointed by the given D must > be the same as B pointed by C pointed by the given D. > > This is not a foreign key, or foreign keys are much more than what I > thought. Looks like a foreign key around the corner to me. Regular referential integrity does not allow you to define that, unless you include the B keys referenced in A and C into separate fields in D and build multi column foreign keys (probably with ON UPDATE CASCADE). The problem arising from that is that you need to know the values ahead or do a lookup and put them into D in a BEFORE trigger. Also I see problems arising if you later want to update A and/or C. I haven't thought it through completely, maybe defining the constraints deferred can help you out of that. Jan > Erwan > > Le lun 02/06/2003 à 15:08, Bruno Wolff III a écrit : >> On Mon, Jun 02, 2003 at 10:52:00 +0200, >> erwan ancel <erwan.ancel@free.fr> wrote: >> > Hi, >> > I would like to know if it is possible to set "complex" constraints on >> > databases such as: >> > >> > A->B means that in table A, each record references a record of table B >> > (or NULL) >> > >> > so we have: >> > >> > A->B >> > C->B >> > D->C >> > D->A >> > constraint: for one record of D, D->A->B = D->C->B >> > >> > Hope it is clear enough. >> >> It looks like you are talking about foreign keys. Postgres has foreign key >> constraints. You can look at the create table documentation to see how >> to define them when creating a table. >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> >>On Mon, Jun 02, 2003 at 10:52:00 +0200, > >> erwan ancel <erwan.ancel@free.fr> wrote: > >>> Hi, > >>> I would like to know if it is possible to set "complex" constraints on > >>> databases such as: > >>> > >>> A->B means that in table A, each record references a record of table B > >>> (or NULL) > >>> > >>> so we have: > >>> > >>> A->B > >>> C->B > >>> D->C > >>> D->A > >>> constraint: for one record of D, D->A->B = D->C->B Could inheritance be used? D -> A -> B D -> C -> B suggests that A and C are similar. I have never tried using inheritance - could you put the constraints on a table which both A and C inherit? Cheers, Patrick
Well, I don't think so: the problem concerns one record of A and one record of C. This means that they would be 2 different records of the table which both A and C inherit. The problem stays. Le dim 08/06/2003 à 21:02, Patrick Welche a écrit : > > >>On Mon, Jun 02, 2003 at 10:52:00 +0200, > > >> erwan ancel <erwan.ancel@free.fr> wrote: > > >>> Hi, > > >>> I would like to know if it is possible to set "complex" constraints on > > >>> databases such as: > > >>> > > >>> A->B means that in table A, each record references a record of table B > > >>> (or NULL) > > >>> > > >>> so we have: > > >>> > > >>> A->B > > >>> C->B > > >>> D->C > > >>> D->A > > >>> constraint: for one record of D, D->A->B = D->C->B > > Could inheritance be used? > > D -> A -> B > D -> C -> B > > suggests that A and C are similar. I have never tried using inheritance - > could you put the constraints on a table which both A and C inherit? > > Cheers, > > Patrick >