PostgreSQL & PHP Tutorials - Database Joins - Foreign Keys
Getting 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.
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!
Avg Rating: 3
Vote Count: 11