[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Conditional foreign key?
From:       Gaetano Mendola <mendola () bigfoot ! com>
Date:       2004-08-31 17:10:46
Message-ID: ch2bev$5b1$1 () floppy ! pyrenet ! fr
[Download RAW message or body]

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









---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
[prev in list] [next in list] [prev in thread] [next in thread]