Foreign key Issues
Hello freinds,
Iam new to mysql I have been facing the problem please let me know ur suggestions.
create table primaryTable(pid int primary key,value int);
insert into primaryTable values(1,1);
mysql> select *from primaryTable;
+-----+-------+
| pid | value |
+-----+-------+
| 1 | 1 |
| 2 | 1 |
+-----+-------+
2 rows in set (0.00 sec)
mysql> create table childTable(cid int primary key,value int,pid int,CONSTRAINT prid_fkey FOREIGN KEY(pid) references primaryTable(pid));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into childTable values(1,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into childTable values(2,2,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into childTable values(3,2,3);
Query OK, 1 row affected (0.00 sec)
mysql> select *from childTable;
+-----+-------+------+
| cid | value | pid |
+-----+-------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 2 | 3 |
+-----+-------+------+
3 rows in set (0.00 sec)
Iam entering pid 3 which is not present in the primaryTable,it is allowing me to enter it.Generally in oracle it will return this error "Server returned error : ERROR (100001): Foreign Key constraint violated!" which it doesnot and
======================================================================
2.Issue
Generally we need to drop the reference table and then the parent table but mysql is allowing drop in any order.Please find below.
mysql> drop table primaryTable;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table childTable;
Query OK, 0 rows affected (0.00 sec)
But in oracle it will give the following error.
Server returned error : ERROR (70002): Drop Table Failed
=======================================================================
3.If i want to use any foreign key as reference first that key should be present as primary key in the parent Table then i can access it as foreign key.Please find below.
create table childTable(cid int primary key,value int,pid int,CONSTRAINT prid_fkey FOREIGN KEY(pid) references primaryTable(pid));
any other database will give the following error
create table primaryTable(pid int primary key,value int); Server returned error :ERROR (3): Table 'PRIMARYTABLE' does not exist in schema
Please let me know your suggestions.
Regards,
Faizan.