Обсуждение: Conditional foreign key?
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
Benjamin Smith wrote: > 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? This is a trigger job not a rule one. Regards Gaetano Mendola