Skip site navigation (1) Skip section navigation (2)

Conditional foreign key?

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 (view raw or flat)
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


Responses

pgsql-general by date

Next:From: David WheelerDate: 2004-08-31 00:16:20
Subject: Re: UTF-8 and LIKE vs =
Previous:From: Tim PenheyDate: 2004-08-30 21:12:12
Subject: Re: Single Row Table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group