repost of how to do select in a constraint
От | Dennis Gearon |
---|---|
Тема | repost of how to do select in a constraint |
Дата | |
Msg-id | 3E2CD569.4D75EB34@cvc.net обсуждение исходный текст |
Список | 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 по дате отправления: