repost of how to do select in a constraint
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
Dennis Gearon wrote:
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'.
Check constraints apparently cannot contain subselects, but they can contain
function calls, so how about something like:
CREATE FUNCTION org_type(int4) RETURNS text AS '
select org_type from OrgTypes where org_type_id = $1;
' LANGUAGE sql;
ALTER TABLE MeetGrpDescs
ADD CONSTRAINT GroupsOnly CHECK(org_type(org_type_id) = 'Group');
--
Peter Gibbs
EmKel Systems
So, the constraint would only check what's being UPDATE'd or INSERT'd, so therefore only a
single value for org_type_id from the INSERT/UPDATE statement would be passed to the function,
correct?
1/20/2003 11:04:35 PM, "Peter Gibbs" <peter@emkel.co.za> wrote:
Show quoted text
Dennis Gearon wrote:
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'.Check constraints apparently cannot contain subselects, but they can contain
function calls, so how about something like:CREATE FUNCTION org_type(int4) RETURNS text AS '
select org_type from OrgTypes where org_type_id = $1;
' LANGUAGE sql;ALTER TABLE MeetGrpDescs
ADD CONSTRAINT GroupsOnly CHECK(org_type(org_type_id) = 'Group');--
Peter Gibbs
EmKel Systems
Dennis Gearon wrote:
So, the constraint would only check what's being UPDATE'd or INSERT'd, so
therefore only a
single value for org_type_id from the INSERT/UPDATE statement would be
passed to the function,
correct?
Correct.
--
Peter Gibbs
EmKel Systems
Can I reference a function in a column check constraint, when the function doesn't yet exist,
i.e. a the time of table creation?
Or is it better to make the table, create the function, then alter table with a check
constraint?
Dennis Gearon wrote:
Can I reference a function in a column check constraint, when the function
doesn't yet exist,
i.e. a the time of table creation?
Or is it better to make the table, create the function, then alter table
with a check
constraint?
The function has to exist before the constraint is created. If the function
depends on the table that will have the constraint, you will need to add the
constraint afterwards; however, if the function only depends on other
tables, then you can create the function before creating the table.
--
Peter Gibbs
EmKel Systems
Thank you VERY, VERY much. Since I am in the planning stages of the database, and don't have
it sited yet, these few questions that I might be able to answer by playing around on a
database hold me up. By answering them, you on this list, Peter in particular, have really
moved my development forward.
1/21/2003 10:11:41 PM, "Peter Gibbs" <peter@emkel.co.za> wrote:
Show quoted text
Dennis Gearon wrote:
Can I reference a function in a column check constraint, when the function
doesn't yet exist,
i.e. a the time of table creation?
Or is it better to make the table, create the function, then alter table
with a check
constraint?
The function has to exist before the constraint is created. If the function
depends on the table that will have the constraint, you will need to add the
constraint afterwards; however, if the function only depends on other
tables, then you can create the function before creating the table.
--
Peter Gibbs
EmKel Systems