Conditional foreign key?
От | Benjamin Smith |
---|---|
Тема | Conditional foreign key? |
Дата | |
Msg-id | 200408301628.21907.bens@effortlessis.com обсуждение исходный текст |
Ответы |
Re: Conditional foreign key?
|
Список | 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
В списке pgsql-general по дате отправления: