repost of how to do select in a constraint - Mailing list pgsql-general

From Dennis Gearon
Subject repost of how to do select in a constraint
Date
Msg-id 3E2CD569.4D75EB34@cvc.net
Whole thread Raw
List 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

pgsql-general by date:

Previous
From: Kevin Brown
Date:
Subject: Re: postmaster.pid
Next
From: Tom Lane
Date:
Subject: Re: postmaster.pid