#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0

    Error Code: 1005 Can't create table 'csm.service_line' (errno: 150)


    I'm getting that error message when trying to Create a Table.

    Here is the code:
    CREATE TABLE SERVICE_LINE
    (
    servLineID INT(5) NOT NULL,
    servNumber INT(5) NOT NULL AUTO_INCREMENT,
    partName VARCHAR(15),
    partDesc VARCHAR(15),
    partPrice DOUBLE,
    partQty INT(3),
    CONSTRAINT service_line_pk PRIMARY KEY (servLineID, servNumber),
    CONSTRAINT service_line_fk FOREIGN KEY (servNumber)
    REFERENCES SERVICE_PERFORMED(servNumber),
    CONSTRAINT service_line_part_fk FOREIGN KEY (partName, partDesc, partPrice, partQty)
    REFERENCES PART(partName, partDesc, partPrice, partQty)
    )
  2. #2
  3. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400
    And that error message would be... what? Something about the servNumber needing to be a primary key?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    And that error message would be... what? Something about the servNumber needing to be a primary key?
    Error code is in the title line
  6. #4
  7. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400
    Yes, but I was looking for the error message which says why it couldn't create the table. After some quick Googling it seems MySQL doesn't actually tell you that kind of useful information.

    You have two foreign keys. There needs to be indexes for the remote fields: one covering <SERVICE_PERFORMED.servNumber> and another covering <PART.partName, PART.partDesc, PART.partPrice, PART.partQty>.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    Yes, but I was looking for the error message which says why it couldn't create the table. After some quick Googling it seems MySQL doesn't actually tell you that kind of useful information.

    You have two foreign keys. There needs to be indexes for the remote fields: one covering <SERVICE_PERFORMED.servNumber> and another covering <PART.partName, PART.partDesc, PART.partPrice, PART.partQty>.
    Yeap, not to much info. I'll try to do that...Thanks for your help...Going to bed now. Will let you now what happens. Once again, thanks...
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by joecruz36
    Yeap, not to much info. I'll try to do that...Thanks for your help...Going to bed now. Will let you now what happens. Once again, thanks...
    Is this what you mean by Indexes to the remote files???

    CREATE TABLE SERVICE_LINE
    (
    servLineID INT(5) NOT NULL,
    servNumber INT(5) NOT NULL AUTO_INCREMENT,
    partName VARCHAR(15),
    partDesc VARCHAR(15),
    partPrice DOUBLE,
    partQty INT(3),
    CONSTRAINT service_line_pk PRIMARY KEY (servLineID, servNumber),
    Code:
    INDEX ( servNumber ),

    CONSTRAINT service_line_fk FOREIGN KEY (servNumber)
    REFERENCES SERVICE_PERFORMED(servNumber),
    Code:
    INDEX ( partName, partDesc, partPrice, partQty ),

    CONSTRAINT service_line_part_fk FOREIGN KEY (partName, partDesc, partPrice, partQty)
    REFERENCES PART(partName, partDesc, partPrice, partQty)
    )
  12. #7
  13. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400
    In the foreign tables. So INDEX(servNumber) in SERVICE_PERFORMED and INDEX(partName, partDesc, partPrice, partQty) in PART.

    But now I see something weird:
    Code:
    servNumber INT(5) NOT NULL AUTO_INCREMENT,
    CONSTRAINT service_line_fk FOREIGN KEY (servNumber) REFERENCES SERVICE_PERFORMED(servNumber),
    Why do you have a foreign key for a column that has AUTO_INCREMENT?
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    I want to establish a relationship with a table that its Primary key is set to be an auto increment value. Is that wrong??
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    [img]C:\Users\Jose\Documents\Inedex.png[/img]
    Last edited by joecruz36; May 21st, 2013 at 08:02 PM. Reason: error
  18. #10
  19. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400
    Foreign key constraints say that a value must exist in the foreign table before they can exist in the... local? native? Local table. Auto-increment columns are for primary keys and the whole point is that the database gives you a new value without you having to do it yourself.

    The two concepts don't mix that way. You can't have MySQL generating ID numbers for you and then enforce that those values had to exist in another table first.

    As to the image... You have to put it somewhere on the Internet. You can't link to images on your computer.

IMN logo majestic logo threadwatch logo seochat tools logo