Re: PHP & PostgreSQL

The following is an example demonstrating how to set up a foreign key constraint in PostgreSQL 7: CREATE SEQUENCE school_id_seq; CREATE TABLE school ( school_id INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'), school_name VARCHAR(80) NOT NULL ); CREATE SEQUENCE student_id_seq; CREATE TABLE student ( student_id INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'), student_name VARCHAR(80) NOT NULL, school_id INT NOT NULL, CONSTRAINT school_exists FOREIGN KEY(school_id) REFERENCES school ON DELETE RESTRICT ); INSERT INTO school (school_name) VALUES ('Alice''s School of Truck Driving'); INSERT INTO school (school_name) VALUES ('Bob''s School of Underwater Knitting'); INSERT INTO student (student_name, school_id) VALUES ('Charlie', 1); INSERT INTO student (student_name, school_id) VALUES ('Doug', 1); INSERT INTO student (student_name, school_id) VALUES ('Ernie', 2); Note the 'ON DELETE RESTRICT' which will prevent you from deleting a school if there is a student going to that school. First look at what's in the tables: SELECT * FROM school; SELECT * FROM student; Now attempt to delete the school: DELETE FROM school WHERE school_id = 1; If you try the above, you should see 'ERROR: school_exists referential integrity violation - key in school still referenced from student' and notice that nothing was deleted. If you want to try the above more than once, the following may be handy: DROP SEQUENCE school_id_seq; DROP TABLE school; DROP SEQUENCE student_id_seq; DROP TABLE student; Cascade & Update Instead of 'ON DELETE RESTRICT' you could specify 'ON DELETE CASCADE'. This would allow the delete (instead of preventing it like in the example above), but it would 'cascade' the delete to the student table so that any students going to the school you deleted would also be deleted. As well as the 'ON DELETE ...' clause you can also specify what is to happen on an update of the foreign key with either: a.. ON UPDATE RESTRICT or b.. ON UPDATE CASCADE ON UPDATE RESTRICT would prevent UPDATE school SET school_id = 20 WHERE school_id = 1 from proceeding if there were any students with a school_id of 1. You might be able to guess that ON UPDATE CASCADE would allow the UPDATE school SET school_id = 20 WHERE school_id = 1 to proceed, but it would also update the school_id field in the student table appropriately. Foreign Keys where the field names are different Consider the following table setup: CREATE SEQUENCE school_id_seq; CREATE TABLE school ( id INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'), name VARCHAR(80) NOT NULL ); CREATE SEQUENCE student_id_seq; CREATE TABLE student ( id INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'), name VARCHAR(80) NOT NULL, school_id INT NOT NULL, CONSTRAINT school_exists FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE RESTRICT ); INSERT INTO school (name) VALUES ('Alice''s School of Truck Driving'); INSERT INTO school (name) VALUES ('Bob''s School of Underwater Knitting'); INSERT INTO student (name, school_id) VALUES ('Charlie', 1); INSERT INTO student (name, school_id) VALUES ('Doug', 1); INSERT INTO student (name, school_id) VALUES ('Ernie', 2); Note how the field names don't match (school.id as opposed to student.school_id); in this case we can put the field name in brackets after the table name. As before, the following will fail: DELETE FROM school WHERE id = 1; - AndrewH ----- Original Message ----- From: "Marcelo Pereira" <peregrin_tuk@bol.com.br> To: <php-general@lists.php.net> Sent: Monday, March 05, 2001 1:00 AM Subject: [PHP] PHP & PostgreSQL Hi, All. I have to build a web site and I was studying postgreSQL, but, whether I am quite wrong or this database does not support Foreign Key. Does anyone use postgreSQL and know how handle Foreign Keys ??? Thanks, Marcelo Pereira Computer Programmer __________________________________________________________________________ O BOL é Top10 no iBest! Vote já para torná-lo Top3! http://www.bol.com.br/ibest.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net