SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    1005 - Can't create table (errno: 150)

    Hi!
    I have read many threads regarding this 150 error, but I cannot find a soluition to my problem. I use server version 5.0.41 and phpmyadmin version 2.10.1. The datatype of the attribute ID in the table Alternativ is the same as in the tables EttAlternativ and FleraAlternativ, IN(11) NOT NULL.The engine type is InnoDB, the three of them are not UNSIGNED and I eaven tried to create an index on the foreign key in the referencing tables. Iīll be very glad for any recomendations I can get to try.Here is my SQL statments:

    Creation of table nr1:

    CREATE TABLE Alternativ (FrageTypKod VARCHAR (50) NOT NULL ,
    ID INT(11) NOT NULL) ENGINE=INNODB;

    ALTER TABLE Alternativ ADD
    CONSTRAINT PK_Alternativ
    PRIMARY KEY (FrageTypKod,ID);

    ALTER TABLE Alternativ ADD
    CONSTRAINT FK_alternativ_fragetyp
    FOREIGN KEY (FrageTypKod)
    REFERENCES FrageTyp(Kod) ON UPDATE CASCADE ON DELETE CASCADE;

    Cration of table nr2:
    CREATE TABLE EttAlternativ (FrageTypKod VARCHAR (50) NOT NULL ,
    AlternativID INT (11) NOT NULL,
    SvarFrageNr INT (11) NOT NULL ,
    SvarPersonRaknare INT (11) NOT NULL,
    SvarOmradeNr VARCHAR (2) NOT NULL) ENGINE=INNODB;

    ALTER TABLE EttAlternativ ADD
    CONSTRAINT PK_EttAlternativ
    PRIMARY KEY (SvarFrageNr,SvarPersonRaknare,SvarOmradeNr);

    ALTER TABLE EttAlternativ ADD
    CONSTRAINT FK_EttAlternativ
    FOREIGN KEY (SvarFrageNr,SvarPersonRaknare,SvarOmradeNr)
    REFERENCES Svar(FrageNr, PersonRaknare, OmradeNr)
    ON UPDATE CASCADE ON DELETE CASCADE;

    ALTER TABLE EttAlternativ ADD
    CONSTRAINT FK_EttAlternativ_fragetyp
    FOREIGN KEY (FrageTypKod)
    REFERENCES FrageTyp(Kod) ON UPDATE CASCADE ON DELETE CASCADE;



    I get the erro: 150 when I run this statment to crate an FK:

    ALTER TABLE EttAlternativ ADD
    CONSTRAINT FK_EttAlternativ_alt
    FOREIGN KEY (AlternativID)
    REFERENCES Alternativ(ID) ON UPDATE CASCADE ON DELETE CASCADE;

    The erro I get is:#1005 - Can't create table '#sql-1801a_502d374.frm' (errno: 150)

    Please help me see the problem...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the table you are referencing does not have a unique index on the ID column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh! Thank you soooo much! Very kind of you to help!

    Would you please explain for me why the index is important here but not on the rest of the tables? I have several tables without index that I refer to them through the foreign keys, but I didnīt get any errors!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    each PRIMARY KEY is a unique index

    i can't see your other tables, but i bet all the foreign keys reference primary keys
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are absolutely right! All of the FKīs refers to PKīs. Not eaven a database teacher could find this problem in my code.

    Thank you very much again, you have been realy helpful.

  6. #6
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again!

    As I have declared tha table Alternative above, mysql doesenīt want to create the primary keys if the ID is an auto increment.

    Do you think it is ok if I make the ID autoincrement by using a trigger that activates every time I make an insert on the table?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by banaz View Post
    As I have declared tha table Alternative above, mysql doesenīt want to create the primary keys if the ID is an auto increment.
    please show the exact CREATE statement that you used, as well as the exact error message

    Quote Originally Posted by banaz View Post
    Do you think it is ok if I make the ID autoincrement by using a trigger that activates every time I make an insert on the table?
    no, i do not

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    O sorry, I forgot

    The table creation code:
    Code:
    CREATE TABLE Alternativ (FrageTypKod VARCHAR (50) NOT NULL ,
                             ID INT (11) NOT NULL AUTO_INCREMENT,
                             Typ VARCHAR (4) NOT NULL) ENGINE=INNODB;
     
     ALTER TABLE Alternativ ADD 
         CONSTRAINT PK_Alternativ
         PRIMARY    KEY (FrageTypKod,ID);
    
    ALTER TABLE Alternativ ADD 
         CONSTRAINT FK_alternativ_fragetyp
         FOREIGN    KEY (FrageTypKod)
         REFERENCES FrageTyp(Kod) ON UPDATE CASCADE ON DELETE CASCADE;
    And the error message:
    Code:
    #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have a primary key defined as a composite of FrageTypKod and ID, yet when you use auto_increment on a column, the value is unique as a single PK.

    I do not know what the values are likely to be for that first col but are they unique? if so, you can have FrageTypKod declared as your PK. perhaps FrageTypKod and Typ would be unique as a composite key and you can then drop the ID column?

    bazz

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i really don't understand why you're getting an error, as it appears that you're doing the same thing as this --
    Code:
    CREATE TABLE animals (
        grp ENUM('fish','mammal','bird') NOT NULL,
        id MEDIUMINT NOT NULL AUTO_INCREMENT,
        name CHAR(30) NOT NULL,
        PRIMARY KEY (grp,id)
    );
    this is the example in the manual here --> http://dev.mysql.com/doc/refman/5.0/...increment.html

    are you sure you want different sets of ID numbers for different FrageTypKods? why?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the content of the table FrageTyp
    Code:
    CREATE TABLE FrageTyp (Kod VARCHAR (50) NOT NULL,
                           PRIMARY KEY (Kod)) ENGINE=INNODB;
    The Kod colmn is unique onley here in this table, because it just tells that there is 4 kind of questions. So in the table Alternative the column and FK FrageTypKod will not be unique, becuse it will be onley one value, Alternative.

    Therfore I need to have the ID column wich hase to be unique.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, but do you really need to do it that way?

    with your composite PK, and let's say for the sake of discussion that the FrageTypKod has values of blue, grey, etc., then your Alternative PK values would be

    blue 1
    blue 2
    blue 3
    blue 4
    grey 1
    grey 2
    grey 3
    etc.

    whereas if you just made the id auto_increment the PK by itself, it would have values 1,2,3,4,5,6,7,etc.
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aha så you mean that it is enough with the column ID autu increment as PK?

    The reason FrageTypKod has to be in the tble Alternativ is that Alternative is derrived(I donīt know if this is the right word) from the table FrageTyp. So the PK in FrageTyp moves down to all tables under it.

    But you mean that itīs not necessary?

    I think it is better if I let you take a look at the ER diagram.
    Attached Images Attached Images

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you mean that the FrageTypKod is a pk in one table and a foreign key in another?

    a PK is a unique key. it does not need to be numeric and it can be made up of more than one column.

    one advantage of using a composite key would be that you will not end up with duplicate entries in the table. I used to use auto_increment thinking it was the only way. now I find it much easier to maintain the db because i can see the true value rather than a number.

    hth

    bazz

  15. #15
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, FrageTypKod is PK in FrageTyp and FK in Alternativ, but it is also a part of the composit PK in Alternativ. So what do you suggest to do in this case?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by banaz View Post
    ... it is also a part of the composit PK in Alternativ.
    why?

    couldn't Alternativ have a different PK?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats why I nedd you to explain it to me according to the ER diagram. Can I just have the ID as PK in Alternative and FrageTypKod as FK?

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by banaz View Post
    Can I just have the ID as PK in Alternative and FrageTypKod as FK?
    you can, yes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Member
    Join Date
    May 2009
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, Thank you once more


Bookmarks

Posting Permissions

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