Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: Adding a foreign key in an existing table

    When adding a foreign key gets an error:
    Error Code: 1005. Can't create table 'itest.#sql-784_18f' (errno: 150)
    I use the syntax:
    Code:
    ALTER TABLE INTR ADD FOREIGN KEY (USER) REFERENCES USERS(ID)
    There is no table itest as is the name of the database. I do not understand this error. What is it?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you provide us with the create statements for the INTR and USERS tables?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by it-iss.com View Post
    Can you provide us with the create statements for the INTR and USERS tables?
    Code:
    CREATE TABLE `intr` (
      `ID` smallint(6) NOT NULL AUTO_INCREMENT,
      `NAZWA` smallint(5) unsigned DEFAULT NULL,
      `DATA` date DEFAULT NULL,
      `OKRES` varchar(2) NOT NULL DEFAULT '',
      `NRWERSJA` varchar(2) NOT NULL DEFAULT '',
      `NRZGLOSZENIA` varchar(2) NOT NULL DEFAULT '',
      `ROK` year(4) DEFAULT NULL,
      `NRWLASNY` varchar(14) NOT NULL DEFAULT '',
      `ILOSCPOZ` smallint(5) unsigned DEFAULT NULL,
      `ND` varchar(1) NOT NULL DEFAULT '',
      `WAGA` varchar(10) NOT NULL DEFAULT '',
      `WARTOSCPLN` decimal(9,2) unsigned DEFAULT NULL,
      `NRSYSTEMOWY` varchar(30) NOT NULL DEFAULT '',
      `IDSYSTEMU` varchar(30) NOT NULL DEFAULT '',
      `RD` varchar(1) NOT NULL DEFAULT '',
      `ISTA` tinyint(1) DEFAULT NULL,
      `ZGLASZAJACY` varchar(25) NOT NULL DEFAULT '',
      `PS` varchar(1) DEFAULT NULL,
      `TRYBPOPRAWY` tinyint(1) DEFAULT NULL,
      `RLUBZFV` tinyint(1) DEFAULT NULL,
      `EMAIL` tinyint(1) DEFAULT NULL,
      `user` varchar(2) DEFAULT NULL,
      UNIQUE KEY `ID` (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
    Code:
    CREATE TABLE `users` (
      `ID` smallint(6) NOT NULL AUTO_INCREMENT,
      `USER` varchar(20) NOT NULL DEFAULT '',
      `HASLO` varchar(10) NOT NULL DEFAULT '',
      UNIQUE KEY `ID` (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off you have not declared the key name, depending on which MySQL you are running this may be an issue

    secondly the datatypes don't match
    intr.user is varchar(2)
    users.id is smallint(6)

    also captialisation may be an issue, there have been incompatabilities with upper of lower case letters, you need to be consistent
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by healdem View Post
    first off you have not declared the key name, depending on which MySQL you are running this may be an issue
    MySQL 5.5
    Sorry, i don`t understand what key name you are mean.

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    you can assign a name to the foreign key but you don't have to. the problem is the mismatched data types as pointed out above.

  7. #7
    Join Date
    Jan 2012
    Posts
    91
    Thanks. I did it.
    Last edited by duf; 09-20-12 at 10:07.

Posting Permissions

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