By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,761 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,761 IT Pros & Developers. It's quick & easy.

Why isn't MySQL enforcing foreign key constraints?

P: n/a
I have created 2 tables in my MySQL database. A_TAB and B_TAB.
They have auto-incrementing integer primary keys respectively named
A_ID & B_ID.
When I created B_TAB, I declared a field named A_ID which references
A_TAB.AID.

I insert valid data into both tables. However, the foreign key
constraint is NOT being enforced. The database allows be to enter any
integer into B_TAB.AID regardless of weather that value exists
anywhere in the field A_TAB.AID.

Why is this constraint being ignored? The whole reason I declared it
was so that it would enforce data validation. What is the point of
having rules that are un-enforced? How can I make this constraint
enforced?
See Below the transcript of my MySQL Session below showing the
problem.

mysql> create table A_TAB
-> (A_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> DUMMY_A INT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> create table B_TAB
-> (B_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> A_ID INT NOT NULL REFERENCES A_TAB(A_ID),
-> DUMMY_B INT NULL
-> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO A_TAB (DUMMY_A) VALUES (1);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO A_TAB (DUMMY_A) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO A_TAB (DUMMY_A) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM A_TAB;
+------+---------+
| A_ID | DUMMY_A |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+---------+
3 rows in set (0.09 sec)

mysql>
mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(11,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(12,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(13,3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(14,4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from B_TAB;
+------+------+---------+
| B_ID | A_ID | DUMMY_B |
+------+------+---------+
| 1 | 1 | 11 |
| 2 | 2 | 12 |
| 3 | 3 | 13 |
| 4 | 4 | 14 |
+------+------+---------+
4 rows in set (0.00 sec)
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hiya

I think it is probably something to do with your table type.

You have to use InnoDB table types to enforce constraints if I remember
correctly

Chris
"Saqib Ali" <sy************@yahoo.com> wrote in message
news:e4**************************@posting.google.c om...
I have created 2 tables in my MySQL database. A_TAB and B_TAB.
They have auto-incrementing integer primary keys respectively named
A_ID & B_ID.
When I created B_TAB, I declared a field named A_ID which references
A_TAB.AID.

I insert valid data into both tables. However, the foreign key
constraint is NOT being enforced. The database allows be to enter any
integer into B_TAB.AID regardless of weather that value exists
anywhere in the field A_TAB.AID.

Why is this constraint being ignored? The whole reason I declared it
was so that it would enforce data validation. What is the point of
having rules that are un-enforced? How can I make this constraint
enforced?
See Below the transcript of my MySQL Session below showing the
problem.

mysql> create table A_TAB
-> (A_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> DUMMY_A INT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> create table B_TAB
-> (B_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> A_ID INT NOT NULL REFERENCES A_TAB(A_ID),
-> DUMMY_B INT NULL
-> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO A_TAB (DUMMY_A) VALUES (1);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO A_TAB (DUMMY_A) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO A_TAB (DUMMY_A) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM A_TAB;
+------+---------+
| A_ID | DUMMY_A |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+---------+
3 rows in set (0.09 sec)

mysql>
mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(11,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(12,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(13,3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO B_TAB (DUMMY_B, A_ID) VALUES
(14,4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from B_TAB;
+------+------+---------+
| B_ID | A_ID | DUMMY_B |
+------+------+---------+
| 1 | 1 | 11 |
| 2 | 2 | 12 |
| 3 | 3 | 13 |
| 4 | 4 | 14 |
+------+------+---------+
4 rows in set (0.00 sec)

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.