error when creating constraint on table.

[charbel]

hi.
im facing an error when executing a script in my application to create constraints on tables . the error msg is :
Script line: 1 Can't create table '.\jmg_acc2008\#sql-3ac_1.frm' (errno: 150)
jmg_acc2008 is my database name.

here is an example on my code:
on btn click :
MyScript1.Execute;

and the script sql :
alter table purchases
add constraint `purchasessupplier_code` foreign key `purchasessupplier_code`(`supplier_code`)
references `suppliers`(`ledger_number`)
on delete restrict;

what is wrong?

[jkuiper_them]

you have forgot something:

Code: Select all

ALTER TABLE `dbdemos`.`purchases` ADD CONSTRAINT `FK_purchases_1` FOREIGN KEY `FK_purchases_1` (`suppl_ID`)
    REFERENCES `supplier` (`suppl_ID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

If you tested your SQL on the database, you will see an error in your query.

[charbel]

i did tested my code in mysql query browser and still have the same error.

this is my code :
ALTER TABLE `jmg_acc2008`.`purchases` ADD CONSTRAINT `FK_Purchase_1` FOREIGN KEY `FK_Purchase_1` (`supplier_code`)
REFERENCES `suppliers` (`ledger_number`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

same error msg : Can't create table '.\jmg_acc2008\#sql-3ac_1e.frm' (errno: 150)

what code i missed?

[jkuiper_them]

Nothing. I think your table is corrupted.
Does it work if you create a new table with the same fields and try to create a foreign key?

[charbel]

Dear sir,
following ur suggestion about corrupted table, i created a new schema, and created the 2 tables . im working in the sql query browser.
here is the code i used :

DROP TABLE IF EXISTS `test_acc2008`.`purchases`;
CREATE TABLE `test_acc2008`.`purchases` (
`Purchase_id` double NOT NULL,
`Supplier_code` varchar(10) default NULL,
PRIMARY KEY (`Purchase_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `test_acc2008`.`suppliers`;
CREATE TABLE `test_acc2008`.`suppliers` (
`Ledger_number` varchar(10) character set utf8 NOT NULL,
`Ledger_name` varchar(50) default NULL,
PRIMARY KEY (`Ledger_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `test_acc2008`.`purchases` ADD CONSTRAINT `FK_Purchase_1` FOREIGN KEY `FK_Purchase_1` (`supplier_code`)
REFERENCES `suppliers` (`ledger_number`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

same error still appear: Can't create table '.\test_acc2008\#sql-3ac_3b.frm' (errno: 150)

[jkuiper_them]

Where is you database running?
Do you have permission to create the index.
It seems that the permission on the file is not correct to create the index.

Try it in the DBDEMOS database as administrator.
I Tried also, but couldn't reproduce the error.
What do they say on the MySQL forum?

[Antaeus]

The reason of the problem is that character sets for the Supplier_code column is not defined explicitly when character set for Ledger_number is defined as utf8. You should define character set in the same way for both columns to resolve this problem.