Foreign Key?

zzz

I'm trying to delete a record from one of my tables and get this error message: Cannot delete or update a parent row: a foreign key constraint fails

What is a foreign key and why would I need one in my db?

mourisj

Assume you have one table defined as
create person (
p_id int PRIMARY KEY,
p_name char(20))

and another

create person_tasks (
task_id int,
p_id int)

you could insert multiple tasks for each person in person_tasks.

p_id is the primary key in person, which makes it is unique in that table

p_id is a foreign key in person_tasks, where it refers to p_id in table person.

If you try to change p_id in person, you will get this kind of error-messages if you have records in person_tasks, as this would break the reference.

Note that you have to define the foreign key constraint with a statement appropriated to your DBMS.

This kind of relationships is one of the most basic concepts of relational databases, an one of their most important features: if you do things (i.e. design your DB ) well, they avoid you to accidentally destroy relationships you create between data, they prevent you referring to non-existing data, etc.

eoghain

Foreign keys are how multiple tables are linked. Lets say I have a table that holds phone numbers and I have another table that hold phone types (i.e. Home, Work, Fax, Modem, Cell, etc.). Using these 2 tables I can figure out that a given phone number is a Cell phone, or a Fax number, or a Work number you get the idea.

Here are the tables:

PhoneTypes:
id int not null auto_increment
name varchar(50)

PhoneNumbers:
id int not null auto_increment
phoneTypeID int not null FK(PhoneTypes)
number varchar(15)

Now since the PhoneNumbers table uses phoneTypeID as a foreign key, if I had a record in PhoneNumbers that was of type Fax and I tried to delete Fax from my PhoneTypes table I would get the error that you are describing.

Foreign Keys are used to maintain data integrity. By using a foreign key you can keep a user from entering invalid data (i.e. I couldn't enter a phone with a type that didn't exist in my PhoneTypes table). Or prevent a user from deleting data that would cause other data to be invalid (i.e. I'd be prevented from deleting the Fax type if a PhoneNumber was using that type).