CODEWITHSUNDEEP
mysqlsql
eastboundr
eastboundr

Reputation: 1877

How come I can enter values freely with foreign key constraint

I created two tables and did the following:

Table 1: (students)

CREATE TABLE student(s int, n int, d int, PRIMARY KEY(s), FOREIGN KEY(d) REFERENCES dep(d));

Table 2: (dep)

CREATE TABLE dep(d int, n int, PRIMARY KEY(d));

So, if i understand correctly, d is a foreign key of table 1 and it references to the primary key of the department. Therefore, The primary key of dep have to match the d in students. However when I do the following

INSERT INTO dep (1,2);

The statement finished with no error? The students table is empty, how could the data be inserted when its primary key is referenced?

Please help, thanks.

By the way I was able to insert into student freely even dep does not have corresponding value. Do you guys think it's because of mysql vs. oracle?

mysql> select * from student;
+---+------+------+
| s | n    | d    |
+---+------+------+
| 5 |    5 | 5    |
+---+------+------+
1 row in set (0.00 sec)

mysql> select * from dep;
+---+------+
| d | n    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

Upvotes: 0

Views: 84

Answers (4)

Devart
Devart

Reputation: 122032

As usual, when you insert data into the detail table and master table has no corresponding values, you got an error - 'Cannot add or update a child row: a foreign key constraint fails...'.

But when the FOREIGN_KEY_CHECKS variable is set to 0, MySQL ignores foreign key constraints -

SET FOREIGN_KEY_CHECKS=0;
INSERT INTO student VALUES (5,5,5); -- no errors
SET FOREIGN_KEY_CHECKS=1;

Upvotes: 0

vikiiii
vikiiii

Reputation: 9476

You can insert into the department table as there is no constraint on this table.

If you will try to add a row in student table with the random department number , which is not present in the department table, then it will give you the constraint error.

Example :If you will try 

insert into student values (1, 4, 1034);

And if there is no row in the department table with the value of primary key 1034 , then it will give the foreign key constraint.

What you need to do is insert your data starting from the parent down.

If you need to delete data you actually have to go the other way, delete the items before you delete the parent order record.

Upvotes: 1

joshuahealy
joshuahealy

Reputation: 3569

You've got your understanding the wrong way round. What you've done in your sql is ensure that when you enter a Student, the value for d must exist as a value in dep.d

if you said 

insert into student values (1, 2, 3)

then this would fail if there was no row in dep with d equal to 3

Upvotes: 2

rikitikitik
rikitikitik

Reputation: 2450

The student table has the foreign key d which is the primary key of the dep table. The student table is the one dependent on the dep table. The dep table has no such dependence on the student table. The constraint is on the student table to have a value of d that should always be in dep table.

Inserting a record into the student table with an invalid value for d WILL cause the error.

Upvotes: 2

Related Questions