Foreign key insert situation

Question

I have two tables: family AND people.

The people table has the following fields:

+----+------+-----+--------+-----------+ | id | name | age | gender | family_id | +----+------+-----+--------+-----------+

The family table has the following fields:

+-----------+ | family_id | +-----------+

The family 'family_id' is a foreign key to id in the people table.

I want to add a new row to people table.

id = 5, name = "Bradd Pit", age = 40, gender = "m"

the id in people table should be equal to family_id in family table

Do I need to make two insert commands? one to family table and one to people table?

edit: family has only one table because when I delete a family -> all the people in the family will also be deleted

the sense is that when i delete a family -> all the people in the family will also be deleted — user3282988
– user3282988, Commented Mar 8, 2014 at 17:50
@user3282988 For that you can use DELETE FROM people where id={family_id}. — Sanoob
– Sanoob, Commented Mar 8, 2014 at 17:52
yes. But i want to add (insert) a new row to people. that means i need to insert family table an id before? — user3282988
– user3282988, Commented Mar 8, 2014 at 17:55
@user3282988 Dude, still you are missing the point. Why do you need a single field table ? If you want to delete all members in a family modify my sql to this DELETE FROM people where family_id={family_id} — Sanoob
– Sanoob, Commented Mar 8, 2014 at 17:59

Bill Karwin · Accepted Answer · 2014-03-08 18:19:46Z

The family 'family_id' is a foreign key to id in the people table.

You have this relationship backwards. The people.family_id should be a foreign key to the family.family_id table.

CREATE TABLE family ( famliy_id INT PRIMARY KEY ); CREATE TABLE people ( id INT PRIMARY KEY, name TEXT, age TINYINT UNSIGNED, gender CHAR(1), family_id INT NOT NULL, FOREIGN KEY (family_id) REFERENCES family(family_id) ON DELETE CASCADE );

The answer to your question is yes, you need to insert to family separately, and before you can insert to people. In SQL, an INSERT statement can insert to only one table at a time.

There could be a way to write a trigger before insert for the people table, which uses INSERT IGNORE to insert the family_id to family just in case it isn't there already (and the IGNORE part means it's not an error if the value is already in that table). But I wouldn't do this, it's needless overhead to run a trigger for this case.

family has only one table because when i delete a family -> all the people in the family will also be deleted

This doesn't require a second family table at all. It's true that the foreign key allows you to DELETE FROM family WHERE family_id = 123, and the cascading delete affects the people table.

But if you didn't have the family table or the foreign key referencing it, you could still delete from the people table, and in fact it would actually be more efficient to simply DELETE FROM people WHERE family_id = 123.

If there's something more complex about your model that you aren't telling us, like multiple child tables referencing family, then it might be worthwhile to have the cascading foreign key to simplify deletions.

koriander · Accepted Answer · 2014-03-08 17:54:14Z

I'm assuming id in table people actually means family_id. In this case, you need two inserts if 5 is not in the family table.

But something about this design is broken. You don't need table family if it has only a field.

Collectives™ on Stack Overflow

Foreign key insert situation

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related