October 24th, 2002, 12:46 PM
Hi all. I'm about to build what I consider to be my first "real" database application, ie. bigger than a couple of users and a few tables, and where I am designing the DB myself from scratch.
I've got a question about foreign keys which I'm hoping someone can help with.
I've got the following tables.
and I am going to create a third table, as follows:
Now, what I'm wondering is if this is a situation in which I would need to use foreign keys to ensure data integrity.
For example if a user with user_id 15 has access to an application with app_id 22 and said user was deleted, I would not want to have the record kept in the wms_access table which referenced user_id 15.
I know the desired results could easily be obtained via straight SQL, but I'd like to see why I should use foreign keys here instead.
Can anyone give any advice here? I'm reading through the section on foreign keys in my PHP/PostgreSQL book but I'd like to get some opinions.
Thanks in advance,
October 24th, 2002, 04:32 PM
You can define the table wms_access this way:
Of course, change the types to match the types of the referenced fields in the foreign table.
create table wms_access
app_id int references wms_apps(app_id) on delete cascade,
user_id int references wms_users(user_id) on delete cascade,
The "on delete cascade" tells Postgres to delete any record in wms_access that matches the deleted record in the referenced table.
October 24th, 2002, 06:44 PM
Foreign keys are a necessary constraint to enforce data integrity - why not use them, as your example is a clear case where they would be useful.
Let the database take care of data integrity as much as possible. The alternative would be to have checks in the application code ... no fun for whoever is programming it.
October 24th, 2002, 07:37 PM
Sheesh, I need to start reading the entire post.. Sorry for the irrelevent help.
To be relevent:
Data is often accessed by a multitude of applications. Meaning any data integrity checks not placed in the data structure itself and enforced by the dbms HAS to be in each and every application. If someone doesn't include that in their application, your data can become corrupted.
October 24th, 2002, 07:57 PM
Thanks all for your posts. The information you provided was exactly what I was looking for. Got it set up and working perfectly on the first try.