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:
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
|