repost of how to do select in a constraint

From: Dennis Gearon <gearond(at)cvc(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: repost of how to do select in a constraint
Date: 2003-01-21 05:06:49
Message-ID: 3E2CD569.4D75EB34@cvc.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I did not get an answer to my question, and I have RTFM'd all six
manuals at this point, so I will try the question again.

I have two tables one essentially a subset of the main table. The subset
adds criteria to the subset of rows from the main table that are
identified by their 'type_id'. The 'type_id' is from another table which
as a Foreign key.

'Orgs' is the main table,
'OrgTypes' is what it says it is, and
'MeetGrpDescs' is the subset table, only for
Orgs of type 'Group'

I'd like to create a table constraint, a trigger, a rule, whatever that
would
prevent insertion or updates of a row that had any other type besides:

'Group'

Into the table 'MeetGrpDescs'. I don't want to hardcode in the 'org_id'
value,
I'd like to put something like a join on org_type_id and then test if
org_type = 'Group'.

How can I do this? Thank you in advance.

-------------Schema of the tables involved (simplified)---------------

CREATE TABLE OrgTypes(
org_type_id serial NOT NULL, -- surrogate primary key of this table
org_type varchar(32) NOT NULL,
-- values will be 'Group','District','Area','Nation' hierarchally
CONSTRAINT PK_OrgTypes1 PRIMARY KEY (org_type_id),
CONSTRAINT UC_OrgTypes1 UNIQUE(org_type));

CREATE TABLE Orgs(
org_id serial NOT NULL, -- surrogate primary key of this table
org_type_id int4 NOT NULL,
org_name varchar(64) NOT NULL,
CONSTRAINT FK_Orgs_1 FOREIGN KEY (org_type_id)
REFERENCES OrgTypes (org_type_id),
CONSTRAINT PK_Orgs1 PRIMARY KEY (org_id,org_type_id),
CONSTRAINT UC_Orgs2 UNIQUE(org_name));

CREATE TABLE MeetGrpDescs( -- only Orgs of type 'Group' Allowed
org_id int4 NOT NULL,
org_type_id int4 NOT NULL,
special_notes text DEFAULT 'none' NOT NULL,
mail_returned bool DEFAULT 0 NOT NULL,
CONSTRAINT FK_MeetGrpDescs_3 FOREIGN KEY (org_id,org_type_id)
REFERENCES Orgs (org_id,org_type_id),
CONSTRAINT UC_Groups2 UNIQUE(org_id));

--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-01-21 05:15:40 Re: postmaster.pid
Previous Message Kevin Brown 2003-01-21 04:49:39 Re: postmaster.pid