MySQL Forums

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.