From: Mike Rylander <mrylander@gmail.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Constraint on 2 column possible?
Date: Thu, 27 Jan 2005 12:02:43 +0000
Message-ID: <b918cf3d050127040219ccba91@mail.gmail.com> (raw)
In-Reply-To: <b918cf3d05012704012e01f2c5@mail.gmail.com>
References: <013b01c50465$93854580$0b00a8c0@forge>
<b918cf3d05012704012e01f2c5@mail.gmail.com>
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <klodoma@ar-sd.net> wrote:
>
> Hi,
>
> I have a table:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL,
> id_wk int4 NOT NULL
> );
>
> CREATE TABLE contact(
> id SERIAL,
> type varchar(20),
> );
>
>
> Now id_hr and id_wk are all referencing the same table contact(id). In the
> contact table I have another column called type.
> How can I write a constraint that checks that id_hr references contact(id)
> and the contact(type='t1')
> and that id_wk references contact(id) and the contact(type='t2').
If I understand what you want, you can do this with a multi-column
foreign key and check constraints.
CREATE TABLE werke1 (
id SERIAL,
id_hr NOT NULL,
hr_contact NOT NULL CHECK (hr_contact = 't1'),
id_wk int4 NOT NULL,
wk_contact NOT NULL CHECK (hr_contact = 't2'),
CONSTRAINT werke1_hr FOREIGN KEY (id_hr,hr_contact) references contact(id,type),
CONSTRAINT werke1_wk FOREIGN KEY (id_wk, wk_contact) references contact(id,type)
);
This will cause the FKEY to match only contact entries that have the
correct combination of id and type.
Hope that helps!
>
> More explicit: the id_hr shows to the id from contact, and this line from
> contact must have the line type='t1'. The same for id_wk just the type is
> another.
>
> I can write:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL references contact(id),
> id_wk int4 NOT NULL references contact(id)
> );
> but how do I check also the type column?
>
> Best regards,
> Andy.
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mrylander@gmail.com
Subject: Re: Constraint on 2 column possible?
In-Reply-To: <b918cf3d050127040219ccba91@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox