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