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: (view raw, whole thread or download thread mbox)
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, 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) 
	(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 FROM customer 
		WHERE featurex=TRUE
	DO ... ? <too many tries to count> 

Any pointers, hints, or info on this kind of statement? 



"I kept looking around for somebody to solve the problem. 
Then I realized I am somebody" 


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-2017 The PostgreSQL Global Development Group