Constraint of only one legal value for a foreign key

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;

Responses

Browse pgsql-general by date

  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 ?