check constraint

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

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 :