0

I have MainTb1 (ID1 int, text varchar(255)) & MainTb2 (ID2 int, text varchar(255)).

I am planning to create Tb1 that will contain these data

ID1 - ID2 - other columns....
12 - 25 - xxxx
24 - 63 - xxx
.....

And create Tb2 that will contain the combined (ID1, ID2) that existed in Tb1.

ID1 - ID2 - other columns....
12 - 25 - xxxx
.....

So, I tried:

create table Tb1 (ID1 int, ID2 int, Foreign key (ID1) references MainTb1(ID1),
 Foreign key (ID2) references MainTb2(ID2) )

create table Tb2 (ID1 int, ID2 int, Foreign key (ID1, ID2) references Tb1(ID1, ID2))

All I want is that the combined keys (ID1, ID2) in Tb2 only exists if there is an exact combined key (ID1, ID2) in Tb1. Ex, in Tb1 we got (ID1=12 & ID2=25) & (24,63), so if user inserts (ID1=12 & ID2=25) into Tb2, then it will be allowed, if user inserts (12,63) int Tb2 then it will not allow.

When creating Tb2 (create table Tb2 (ID1 int, ID2 int, Foreign key (ID1, ID2) references Tb1(ID1, ID2))), I got 150 error in MySQL5.0, but seem if i use Mysql5.5 then there is no problem. But I can't change new Mysql version cos it's legacy system.

So How to force Foreign Key (ID1, ID2) in old MySQL such as 5.0 or 5.1?

Tum
  • 3,614
  • 5
  • 38
  • 63

1 Answers1

0

You can separate the keys from the table creation. Just put an alter table statement after your create table statement. In that alter table you can add contraint foreign key ....

K_B
  • 3,668
  • 1
  • 19
  • 29
  • i have the same err, err 150 – Tum Jan 13 '14 at 20:32
  • Have you checked the following: http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 – K_B Jan 14 '14 at 12:32