The idea here is that a wolf can attack a sheep, or a wolf can attack another wolf, but sheep can't attack anything. I suppose I could list each wolf in both the predator and prey tables, but that seems a bit duplicative (and causes other problems). ...Robert -----Original Message----- From: David Fetter [mailto:david@xxxxxxxxxx] Sent: Monday, February 19, 2007 1:04 PM To: Robert Haas Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] complex referential integrity constraints On Fri, Feb 16, 2007 at 09:58:56AM -0500, Robert Haas wrote: > So, I have the following problem. > > Suppose you have two kinds of animals, sheep and wolves. Since they > have very similar properties, you create a single table to hold both > kinds of animals, and an animal_type table to specify the type of each > animal: > > CREATE TABLE animal_type ( > id integer not null, > name varchar(80) not null, > primary key (id) > ); > INSERT INTO animal_type VALUES (1, 'Sheep'); > INSERT INTO animal_type VALUES (2, 'Wolf'); > > CREATE TABLE animal ( > id serial, > type_id integer not null references animal_type (id), > name varchar(80) not null, > age integer not null, > weight_in_pounds integer not null, > primary key (id) > ); > > The animal_type table is more or less written in stone, but the animal > table will be updated frequently. Now, let's suppose that we want to > keep track of all of the cases where one animal is mauled by another > animal: > > CREATE TABLE mauling ( > id serial, > attacker_id integer not null references animal (id), > victim_id integer not null references animal (id), > attack_time timestamp not null, > primary key (id) > ); > > The problem with this is that I have a very unsettled feeling about the > foreign key constraints on this table. The victim_id constraint is > fine, but the attacker_id constraint is really inadequate, because the > attacker CAN NEVER BE A SHEEP. I really want a way to write a > constraint that says that the attacker must be an animal, but > specifically, a wolf. > > It would be really nice to be able to write: > > FOREIGN KEY (attacker_id, 2) REFERENCES animal (id, type_id) > > Or: > > CREATE UNIQUE INDEX wolves ON animal (id) WHERE type_id = 2; > -- and then > FOREIGN KEY (attacker_id) REFERENCES INDEX wolves > > ...but that's entirely speculative syntax. I don't think there's any > easy way to do this. (Please tell me I'm wrong.) > > The problem really comes in when people start modifying the animal > table. Every once in a while we have a case where we record something > as a wolf, but it turns out to have been a sheep in wolf's clothing. In > this case, we want to do something like this: > > UPDATE animal SET type_id = 1 WHERE id = 572; > > HOWEVER, this operation MUST NOT be allowed if it turns out there is a > row in the mauling table where attacker_id = 572, because that would > violate my integrity constraints that says that sheep do not maul. > > Any suggestions? I've thought about creating rules or triggers to check > the conditions, but I'm scared that this could either (a) get really > complicated when there are a lot more tables and constraints involved or > (b) introduce race conditions. > > Thanks, > > ...Robert I'd do something like this: CREATE TABLE animal_type ( animal_name TEXT PRIMARY KEY, CHECK(animal_name = trim(animal_name)) ); /* Only one of {Wolf,wolf} can be in the table. */ CREATE UNIQUE INDEX just_one_animal_name ON animal_type(LOWER(animal_name)); CREATE TABLE predator ( animal_name TEXT NOT NULL REFERENCES animal_type(animal_name) ON DELETE CASCADE, PRIMARY KEY(animal_name) ); CREATE TABLE prey ( animal_name TEXT NOT NULL REFERENCES animal_type(animal_name) ON DELETE CASCADE, PRIMARY KEY(animal_name) ); CREATE TABLE mauling ( id SERIAL PRIMARY KEY, attacker_id INTEGER NOT NULL REFERENCES predator (animal_type_id), victim_id INTEGER NOT NULL REFERENCES prey (animal_type_id), attack_time TIMESTAMP WITH TIME ZONE NOT NULL ); Cheers, D -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
