From: | Dennis Gearon <gearond(at)cvc(dot)net> |
---|---|
To: | "Lista POSTGRESQL" <pgsql-general(at)postgresql(dot)org> |
Subject: | Constraint of only one legal value for a foreign key |
Date: | 2003-01-17 20:18:18 |
Message-ID: | RLGQZHD81VZYQN51ZWHCTQ83WQPK.3e28650a@cal-lab |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have three tables (actually more :-)
Orgs
OrgTypes
MeetGrpDescs
OrgTypes is a foreign key in Orgs, and says what type of Org an Org is
It can be one of these:
'World Service Office', 'Area', 'District',
'Group', 'Region', 'AIS', 'LDC', 'AIS/LDC'
MeetGrpDescs is a table with various further attributes of ONLY the
Orgs with type of 'Group'.
How do I enforce that the records inserted into MeetGrpDescs are:
1/ An Org (done by foreign key constraints aleady, correct?)
2/ Are of type 'Group'
Here is the DDL for the three tables:
/* organizations -----------------------------------------*/
CREATE TABLE Orgs(
org_id serial NOT NULL,
org_type_id int4 NOT NULL,
grp_type_id int4,
grp_status_id int4,
org varchar(64) NOT NULL,
wsoid varchar(16) DEFAULT 'none' NOT NULL,
created timestamp NOT NULL,
modified timestamp,
archived timestamp,
CONSTRAINT FK_An_Org_Has_An_OrgType_1 FOREIGN KEY (org_type_id) REFERENCES OrgTypes (org_type_id),
CONSTRAINT FK_An_Org_Which_Has_OrgType_Of_Group_Has_A_MeetGrpDesc_2 FOREIGN KEY
(grp_type_id,grp_status_id) REFERENCES MeetGrpDescs (grp_type_id,grp_status_id),
CONSTRAINT PK_Orgs1 PRIMARY KEY (org_id,org_type_id));
COMMENT ON COLUMN Orgs.org_id IS 'integer surr primary key';
COMMENT ON COLUMN Orgs.org_type_id IS 'integer foreign key, for relating an OrgType to zero to
many Orgs';
COMMENT ON COLUMN Orgs.org IS 'Name of Org(en, utf-8)';
COMMENT ON COLUMN Orgs.wsoid IS 'needs a constraint where the value is 'none' or unique among
values that are NOT 'one' CHANGE THIS TO -1 if no number and positive if IS number, if WSOID is a
pure number.';
CREATE INDEX IDX_WSOID ON Orgs (wsoid);
/* type of organizations -----------------------------------------*/
CREATE TABLE OrgTypes(
org_type_id serial NOT NULL CONSTRAINT PK_OrgTypes1 PRIMARY KEY,
org_type varchar(32) NOT NULL,
wsoid_reqd bool NOT NULL,
created timestamp NOT NULL,
modified timestamp,
archived timestamp,
CONSTRAINT UC_OrgTypes1 UNIQUE(org_type));
COMMENT ON COLUMN OrgTypes.org_type_id IS 'integer surr primary key';
COMMENT ON COLUMN OrgTypes.org_type IS 'Name of OrgType(en, utf-8)';
COMMENT ON COLUMN OrgTypes.wsoid_reqd IS '0 if WSOID *NOT* required, 1 if WSOID *IS* required';
/* Descriptions for Orgs of type 'Group' organizations -----------------------------------------*/
CREATE TABLE MeetGrpDescs(
org_id int4 NOT NULL,
org_type_id int4 NOT NULL,
grp_type_id int4 NOT NULL,
grp_status_id int4 NOT NULL,
special_notes text DEFAULT 'none' NOT NULL,
mail_returned bool DEFAULT 0 NOT NULL,
created timestamp NOT NULL,
modified timestamp,
archived timestamp,
CONSTRAINT FK_A_MeetGrp_Desc_Has_A_Grp_Status_1 FOREIGN KEY (grp_status_id) REFERENCES GrpStatuses
(grp_status_id),
CONSTRAINT FK_A_MeetGrpDesc_Has_A_ValidGrpType_2 FOREIGN KEY (grp_type_id) REFERENCES
ValidGrpTypes (grp_type_id),
CONSTRAINT FK_An_Org_Which_Has_OrgType_Of_Group_Has_A_MeetGrpDesc_3 FOREIGN KEY
(org_id,org_type_id) REFERENCES Orgs (org_id,org_type_id),
CONSTRAINT PK_MeetGrpDescs1 PRIMARY KEY (org_id,org_type_id,grp_type_id,grp_status_id));
COMMENT ON COLUMN MeetGrpDescs.org_id IS 'integer foreign key, for relating an Org of type
'group' to zero to many MeetGrpDescs';
COMMENT ON COLUMN MeetGrpDescs.org_type_id IS 'integer foreign key, for: 1/ reference integrity
deletions, 2/ verifying only orgs with 'group' type in this table';
COMMENT ON COLUMN MeetGrpDescs.grp_type_id IS 'integer foreign key, for relating a ValidGrpType to
zero to many MeetGrpDescs';
COMMENT ON COLUMN MeetGrpDescs.grp_status_id IS 'integer foreign key, for relating a GrpStatus to
zero to many MeetingGrpDescs';
COMMENT ON COLUMN MeetGrpDescs.special_notes IS 'special entrance directions, babysitting, etc(en,
utf-8)';
COMMENT ON COLUMN MeetGrpDescs.mail_returned IS '0 if no mail recently returned, NOT 0 if mail
recently rerturned. 'recently' to be defined';
/* Fixed values for 'OrgTypes' table */
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'World Service Office', 0;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'Area', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'District', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'Group', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'Region', 0;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'AIS', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'LDC', 1;
INSERT INTO OrgAddrTypes( org_type, wsoid_reqd ) VALUES 'AIS/LDC', 1;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-01-17 20:20:06 | Re: point -> double,double ? |
Previous Message | Greg Stark | 2003-01-17 20:02:55 | Re: point -> double,double ? |