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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
