Conditional foreign key?

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

Предыдущее
От: Josué Maldonado
Дата:
Сообщение: update trigger performance
Следующее
От: David Wheeler
Дата:
Сообщение: Re: UTF-8 and LIKE vs =