Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    110

    Unanswered: Foreign key constraint problem

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    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 ?
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Mar 2004
    Posts
    33

    Thumbs up Scripts

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •