or 02-9262-7770 
| Database Joins | |
By Chris Smith |
Published
03/8/2006
|
Programming
|
Rating:
|
|
|
Foreign KeysGetting off topic again, in database terminology this is called a "foreign key" and allows the database itself to do some integrity checks. What does that mean? Well, it means the database can check that you have a valid author for each news item. I'll probably get in trouble for saying this but MySQL doesn't support foreign keys properly at all, so be very wary - you can get bad data into your database even when using foreign key constraints (no matter which MySQL "storage engine" you use). PostgreSQL supports foreign keys fully and will stop bad data from entering your system. We need to add an author to the database first, just like this: INSERT INTO authors(authorid, username, firstname, lastname) VALUES (1, 'Chris', 'Chris', 'Smith'); Now that we have an author, we can add a news item: INSERT INTO newsitem(newsid, newstitle, newscontent, authorid) VALUES (1, 'Test', 'Testing News', 1); If we try to add a newsitem for an author that doesn't exist: INSERT INTO newsitem(newsid, newstitle, newscontent, authorid) VALUES (2, 'Test Missing Author', 'Testing News for Missing Author', 2); ... then you should get an error (this is from postgresql 8.1.1): ERROR: insert or update on table "newsitem" violates foreign key constraint "newsitem_authorid_fkey" DETAIL: Key (authorid)=(2) is not present in table "authors". This stops us from adding bad data! |
|
