Hello everybody

I`m trying to create two tables. The SQl statements I use are given below :

CREATE TABLE TYPOS(Kwd_Typou VARCHAR(7) NOT NULL,
Onomasia VARCHAR(15) NOT NULL,
INDEX Onomasia_ind (Onomasia),
PRIMARY KEY(Kwd_Typou)) TYPE=INNODB;

CREATE TABLE MONTELO(Kwd_Montelou VARCHAR(7) NOT NULL,
Kwd_Typou VARCHAR(7) NOT NULL,
Onomasia VARCHAR(30) NOT NULL,
Kataskeyasths VARCHAR(30),
Antiproswpos VARCHAR(30),
INDEX Kwd_Typou_ind (Kwd_Typou),
INDEX Onomasia_ind (Onomasia),
INDEX Kataskeyasths_ind (Kataskeyasths),
FOREIGN KEY(Kwd_Typou) REFERENCES TYPOS(Kwd_Typou) ON DELETE SET NULL,
PRIMARY KEY(Kwd_Montelou)) TYPE=INNODB;

i get an errno 150 on the second statement. If you`ve noticed the foreign key in the second table is also a primary key of the first table! Is there something special in this case? Any ideas on how I can create the second table with the constraints shown in the code?

thx, in advance

George Papadopoulos

Normaly this error show that there are a problem with the index in the foreign key constraint

But you use an index with your FK

What is your MySql Version ?

Try this script:

DROP TABLE IF EXISTS typos;
CREATE TABLE `typos` (
`Kwd_Typou` varchar(7) NOT NULL default '',
`Onomasia` varchar(15) NOT NULL default '',
PRIMARY KEY (`Kwd_Typou`),
KEY `Onomasia_ind` (`Onomasia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS montelo;
CREATE TABLE `montelo` (
`Kwd_Montelou` varchar(7) NOT NULL default '',
`Kwd_Typou` varchar(7) NOT NULL default '',
`Onomasia` varchar(30) NOT NULL default '',
`Kataskeyasths` varchar(30) default NULL,
`Antiproswpos` varchar(30) default NULL,
PRIMARY KEY (`Kwd_Montelou`),
KEY `Kwd_Typou_ind` (`Kwd_Typou`),
KEY `Onomasia_ind` (`Onomasia`),
KEY `Kataskeyasths_ind` (`Kataskeyasths`),
CONSTRAINT `montelo_ibfk_1` FOREIGN KEY (`Kwd_Typou`) REFERENCES `typos` (`Onomasia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;