On Wednesday 19 February 2003 10:46, [EMAIL PROTECTED] wrote: > I have problems with foreign key creation. > I have installed mysql server cersion 3.23.53 on red hat linux 7.3. > Then I have created innodb datebase to be able to use FOREIGN KEY > Constarints. > This is concerning piece of my my.cnf file: >
[skip] > > Then I restarted MySql databas eserver (everything looked ok in logfie) > But I am not able to create tables with foreign keys. please have a look at > this error: > > mysql> use test; > Database changed > mysql> CREATE TABLE SMS_STATUS ( > -> SS_ID VARCHAR(50) NOT NULL, > -> SS_DESCRIPTION TEXT, > -> PRIMARY KEY (SS_ID)) > -> TYPE = INNODB; > Query OK, 0 rows affected (0.01 sec) > > mysql> CREATE TABLE SMS_IN ( > -> SI_ID BIGINT(12) NOT NULL AUTO_INCREMENT, > -> SI_CONTENT VARCHAR(160), > -> SI_ANUMBER VARCHAR(30) NOT NULL, > -> SI_BNUMBER VARCHAR(30) NOT NULL, > -> SI_DATE_SENT DATETIME NOT NULL, > -> SI_DATE_RECEIVED TIMESTAMP NOT NULL, > -> SI_FK_SS_ID VARCHAR(50), > -> PRIMARY KEY (SI_ID)) > -> TYPE = INNODB; > Query OK, 0 rows affected (0.01 sec) > > mysql> ALTER TABLE SMS_IN > -> ADD CONSTRAINT FOREIGN KEY (SI_FK_SS_ID) > -> REFERENCES SMS_STATUS(SS_ID) > -> ON DELETE SET NULL; > ERROR 1005: Can't create table './test/#sql-1536_5.frm' (errno: 150) > > > The same happens when I insert foreign key into the create statement. > Does anybody knows what is the problem, and what should I do to correct > this problem? >From the MySQL manual: Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
