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

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

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