Thread: Any plans on allowing user-defined triggers to be deferrable?
Well, I guess the only moments when you can verify those conditions are when you insert or delete a project, because it seems logical to me that, at the moment of a department insertion, you will not have any projects related to it, so the constraint you need will automatically be broken.
So, imho, what you can do is to prevent the system to delete a project when there are only two in a department, or to insert a new one when there are 8 projects already there.
On Tue, 2004-10-26 at 16:18, Mike Mascari wrote:
So, imho, what you can do is to prevent the system to delete a project when there are only two in a department, or to insert a new one when there are 8 projects already there.
On Tue, 2004-10-26 at 16:18, Mike Mascari wrote:
Valentin Militaru wrote: > You can do that. But first you have to do some optimisations, like: > add a column id(bigserial) to the departamens table, after which you > will replace the column department with id_department in the projects > table. It is an optimisation, as you are dealing with integer, not text. Well, that's an argument for surrogate keys, which will invoke a philosophical war amongst purists that I won't touch... > After that, what do you want to achieve? When you are inserting a > department, should the server insert 2 to 8 blank records in the > projects table which contain the inserted department? Or do you want not > to be able to insert a department if there aren't already 2 to 8 > projects containing that department in the projects table? I want the database to enforce logical consistency by ensuring that if a department exists, there are at a minimum two projects and a maximum of eight projects associated with it. Date & Darwen attribute the enforcement of such business requirements to database constraints. PostgreSQL lacks database constraints, but the result can often be achieved through triggers. But I can't figure out how to enforce consistency without deferrable triggers and without relying on the application to maintain consistency, which is not its job. Mike Mascari
|
Valentin Militaru valentin.militaru@telcor.ro SC Telcor Communications SRL Tel. fix: 0316900015 Fax: 0316900001 Telefon mobil: 0741168267 |
Attachment
You can do that. But first you have to do some optimisations, like:
add a column id(bigserial) to the departamens table, after which you will replace the column department with id_department in the projects table. It is an optimisation, as you are dealing with integer, not text.
After that, what do you want to achieve? When you are inserting a department, should the server insert 2 to 8 blank records in the projects table which contain the inserted department? Or do you want not to be able to insert a department if there aren't already 2 to 8 projects containing that department in the projects table?
On Tue, 2004-10-26 at 15:55, Mike Mascari wrote:
add a column id(bigserial) to the departamens table, after which you will replace the column department with id_department in the projects table. It is an optimisation, as you are dealing with integer, not text.
After that, what do you want to achieve? When you are inserting a department, should the server insert 2 to 8 blank records in the projects table which contain the inserted department? Or do you want not to be able to insert a department if there aren't already 2 to 8 projects containing that department in the projects table?
On Tue, 2004-10-26 at 15:55, Mike Mascari wrote:
I'd like to ensure that the creation of a department also implies the creation of two to eight projects; no more, no less: CREATE TABLE departments ( department text primary key not null ); CREATE TABLE projects ( project text primary key not null, department text not null references departments(department) on delete cascade on update cascade ); So it'd be nice to have an INSERT trigger that's fired on departments at the end of the transaction to ensure that between two and eight projects exist for the newly created department. Is there no way to achieve the above stated goal in the server? Must I rely on the application to enforce consistency? Mike Mascari ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
|
Valentin Militaru valentin.militaru@telcor.ro SC Telcor Communications SRL Tel. fix: 0316900015 Fax: 0316900001 Telefon mobil: 0741168267 |
