I partially agree: If people CAN do stupid things, they are 'clever' enough to find a way to actually do it. I've seen them destroy things, by just using a system in a way it was not intended. They effectively found a way to blow away the very thing that part was designed for. But indeed, it's a lot of work, especially if the number of tables that must be referenced increases. I'm a strong supporter for ensuring consistency. Postgres has what it takes to do the job, but it doesn't make my life a lot easier. But it seems to be as good as it gets today... Perhaps we should rather define a 'database' constraint in the order of: "For every mauling, the attacking animal must be of the attacker type" (in a computer understandable manner). From the set theory this should be possible without too much problems, However doing so efficiently might be slightly harder. This might be a fun project and useful for the TODO list. At least it makes it a lot easier (and maintanable) to enforce database-wide constraints. - Joris >-----Original Message----- >From: Robert Haas [mailto:Robert.Haas@xxxxxxxxxx] >Sent: woensdag 21 februari 2007 3:37 >To: Joris Dobbelsteen; elein >Cc: pgsql-general@xxxxxxxxxxxxxx >Subject: RE: [GENERAL] complex referential integrity constraints > >Yes, exactly. And while you might not care about all of those >(e.g. I care about the first two but am not worried about the >third one because I'm the only one who will ever update that >table), writing multiple triggers to enforce each constraint >of this type quickly gets old if there are even a few of them. > It is exponentially harder to write a constraint of this type >than it is to write a simple foreign key constraint. > >...Robert > >-----Original Message----- >From: Joris Dobbelsteen [mailto:Joris@xxxxxxxxxxxxxxxxxxxxx] >Sent: Monday, February 19, 2007 5:59 AM >To: elein; Robert Haas >Cc: pgsql-general@xxxxxxxxxxxxxx >Subject: RE: [GENERAL] complex referential integrity constraints > >>Why don't you add a field in animal_types that is boolean mauler. >>Then you can add a trigger on the mauling table to raise an >error when >>the attacker_id is an animal type mauler. > >This is only partial. You need a lot more triggers to >guarentee the constraints are enforced. >Precisely you need to validate: >* mauling on insert/update of attacker_id >* animal on update of type_id >* animal_type on update of your property > >Of course you need to think about the MVCC model, such that: >Transaction 1 executes >INSERT INTO mauling VALUES ('someattacker'), Transaction 2 >executes UPDATE animal_type SET mauler = false WHERE name = >'someattacker', such that both transaction happen in parallel. > >This is perfectly possible and will make it possible to >violate the constraint, UNLESS locking of the tuples is done correctly. > >These contraints are not trivial to implement (unfortunally). >It would be great if they where. > >- Joris >
