From: | Benjamin Smith <bens(at)effortlessis(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Conditional foreign key? |
Date: | 2004-08-30 23:28:21 |
Message-ID: | 200408301628.21907.bens@effortlessis.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
We have a list of customers, some of whom have purchased feature X and some of
whom have not. If a customer has paid for featurex, they can use it, and a
strict relationship between cust_items.items_id and items.id, but only if
they are signed up to use featurex, otherwise I want cust_items.items_id to
be NULL.
Currently, I have tables defined similar to:
create table Customer (
id serial unique not null,
name varchar(30) unique not null,
FeatureX bool not null
);
Create table cust_items (
id serial unique not null,
customer_id integer not null references customer(id),
name varchar(30) not null,
type varchar not null,
items_id integer default null references featurex(id),
cust_active bool not null
);
// type is one of "book", "tape", or "featurex"
Create table items (
id serial not null unique,
title varchar(30)
);
I want to say
"If the cust_items.type='featurex' then (
(customer.featurex must be true)
AND
(cust_items.items_id must be in
(select id from items)
)";
I'm just stumped as to how to say this.
I've tried, with the above table defs,
CREATE RULE check_customer ON UPDATE to cust_items
WHERE NEW.type='featurex' AND
NEW.customer_id IN
(SELECT customer.id FROM customer
WHERE featurex=TRUE
)
DO ... ? <too many tries to count>
Any pointers, hints, or info on this kind of statement?
Thanks,
Ben
--
"I kept looking around for somebody to solve the problem.
Then I realized I am somebody"
-Anonymous
From | Date | Subject | |
---|---|---|---|
Next Message | David Wheeler | 2004-08-31 00:16:20 | Re: UTF-8 and LIKE vs = |
Previous Message | Tim Penhey | 2004-08-30 21:12:12 | Re: Single Row Table? |