MySQL Forums
Forum List  »  Ruby

Foreign key referral integrity
Posted by: shyam eclips
Date: August 08, 2008 02:06PM

Hi,

mysql> select * from sample;
+-----+--------+------------+
| sno | doorno | DBO |
+-----+--------+------------+
| 10 | 5 | 1981-01-09 |
| 20 | NULL | 1981-01-09 |
| 30 | NULL | 1984-08-29 |
| 40 | 4 | 1984-08-29 |
| 50 | 4 | 1985-08-29 |
| 60 | 4 | NULL |
+-----+--------+------------+
6 rows in set (0.00 sec)

mysql> create table sample1(pno int primary key,sno int references sample(sno),p
name varchar(20));
Query OK, 0 rows affected (0.32 sec)

(After insert 3 rows in sample1 tables)

mysql> select * from sample1;
+-----+------+---------+
| pno | sno | pname |
+-----+------+---------+
| 1 | 10 | shyam |
| 2 | 10 | welcome |
| 3 | 20 | welcome |
+-----+------+---------+
3 rows in set (0.00 sec)

mysql> delete from sample where sno=10;
Query OK, 1 row affected (0.10 sec)


mysql> select * from sample;
+-----+--------+------------+
| sno | doorno | DBO |
+-----+--------+------------+
| 20 | NULL | 1981-01-09 |
| 30 | NULL | 1984-08-29 |
| 40 | 4 | 1984-08-29 |
| 50 | 4 | 1985-08-29 |
| 60 | 4 | NULL |
+-----+--------+------------+
5 rows in set (0.00 sec)

mysql> select * from sample1;
+-----+------+---------+
| pno | sno | pname |
+-----+------+---------+
| 1 | 10 | shyam |
| 2 | 10 | welcome | (Here 10 can't delete)
| 3 | 20 | welcome |
+-----+------+---------+
3 rows in set (0.00 sec)

mysql> insert into sample1 values(5,15,'welcome');
Query OK, 1 row affected (0.03 sec)

(Here primary key didnt have '15' in sample table even thow its inserted)

mysql> select * from sample1;
+-----+------+---------+
| pno | sno | pname |
+-----+------+---------+
| 1 | 10 | shyam |
| 2 | 10 | welcome |
| 3 | 20 | welcome |
| 5 | 15 | welcome |
+-----+------+---------+
4 rows in set (0.00 sec)


I have tried this example but I can delete and add ID's to each table without any type of 'foreign key error' occurring. Ex. I can add user 1 to each table and then delete from the users table without getting an error. Also, I can insert an SNO(15) to profiles that is not in the sample table.

Here primary and foreign key rules are not working, I am using mysql 5.2. please help me soon, how to rectify this problem.
by
S.Shyam.

Options: ReplyQuote


Subject
Written By
Posted
Foreign key referral integrity
August 08, 2008 02:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.