Foreign Key

strozzi

Hi...i have a trouble inserting a FOREIGN KEY in a a table type INNO
DB that REFERENCES other INNO DB table...
the syntax is correct but
the console give me this error:
ERROR 1005: Can't create table
'.\works_management#sql-468_67.frm' (errno: 150)

what i have to
check for??

tnx in advance

melody

Innodb error code of 150 means the foreign key constraint is malformed... I've also heard of 150 error codes thrown out after wrong settings have been placed on the db. post the SQL here and we can take a look at what's wrong with it...

strozzi

Table contatti:

CREATE TABLE contatti (
id_contatto bigint(20) NOT NULL auto_increment,
nome_contatto varchar(50) NOT NULL default '',
cognome_contatto varchar(50) NOT NULL default '',
dob_contatto date NOT NULL default '0000-00-00',
citta_di_residenza_contatto varchar(50) NOT NULL default 'Terni',
cf_contatto varchar(20) NOT NULL default '',
link_web_contatto varchar(100) NOT NULL default '',
PRIMARY KEY (id_contatto),
UNIQUE KEY cf_contatto (cf_contatto),
KEY cf_contatto_index (cf_contatto)
) TYPE=InnoDB COMMENT='tab contatti main' AUTO_INCREMENT=1 ;

Table ditta_contatt0:

CREATE TABLE ditta_contatto (
id_contatto int(11) NOT NULL default '0',
nome_ditta_contatto varchar(50) NOT NULL default '',
piva_contatto varchar(20) NOT NULL default '',
link_web_ditta_contatto varchar(100) NOT NULL default '',
PRIMARY KEY (id_contatto),
UNIQUE KEY piva_contatto (piva_contatto),
KEY piva_contatto_index (piva_contatto)
) TYPE=InnoDB COMMENT='ditta contatto';

the foreign key command:

ALTER TABLE ditta_contatto
ADD CONSTRAINT contatto FOREIGN KEY (id_contatto) REFERENCES contatti(id_contatto) ON UPDATE NO ACTION ON DELETE SET NULL;

I hope will help...strange.....in postGreSql works all good and easy....

bye

Lars_Berg

id_contatto bigint(20)

id_contatto int(11)

The columns must have the same data type.

strozzi

nothing change.....
now is the same data type and give me the same error....

tnx the same

strozzi

All done....there was an syntax error in porting Foreign jey From PostGreSql to Mysql....why they make it un-portable ??

Tnx the same
Bye