Question 252783 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why Can't create table in this way?

I have a set of sql statements,

CREATE TABLE PRICING(
    CodeVillage INT,
    TypeCabin INT,
    CodeSeason CHAR(2),
    WeekPrice INT,
    PRIMARY KEY(CodeVillage, TypeCabin, CodeSeason)
);

CREATE TABLE SEASON(
    CodeSeason CHAR(2) PRIMARY KEY,
    NameSeason VARCHAR(20),
    CONSTRAINT Fk_Pricing_CodeSeason Foreign KEY (CodeSeason)
    REFERENCES PRICING(CodeSeason)
);

The sql gave me tips that

ERROR 1005 (HY000): Can't create table 'france.season' (errno: 150)

I know that the problem is on the foreign key but I don’t know why, could you help me figure out? This is just the creating matters, and the table pricing has no data in it.

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    I add an index after the definition part of the Pricing and it works!

    CREATE INDEX idx_CodeSeason ON PRICING (CodeSeason);

    This is from the message in the LATEST FOREIGN KEY ERROR part, thanks for answers by Georg Richter who gave me inspirations. So I think the problem is that the CodeSeason is on the 3rd part of the Primary Key, and the sql engine requires a first position based index or primary key or so.


  2. 0

    Since the error isn’t really verbose, execute

    SHOW ENGINE INNODB STATUS

    instead, which let you know some more details under section LATEST FOREIGN KEY ERROR:

    ------------------------
LATEST FOREIGN KEY ERROR
------------------------
2023-09-26 09:26:17 0x1910 Error in foreign key constraint of table `test_db`.`season`:
Create  table `test_db`.`season` with foreign key `Fk_Pricing_CodeSeason` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
    Login or Signup to reply.
  3. 0

    A foreign key establishes a 1:n relation. The parent table has a primary key, so each row is uniquely identifyable. The child table has a foreign key referencing this primary key, so as to say which parent row a child row belongs to.

    You have mistakenly tried to place the foreign key in the parent table.

    CREATE TABLE season
(
    CodeSeason CHAR(2) PRIMARY KEY,
    NameSeason VARCHAR(20)
);

CREATE TABLE pricing
(
    CodeVillage INT,
    TypeCabin   INT,
    CodeSeason  CHAR(2),
    WeekPrice   INT,
    PRIMARY KEY(CodeVillage, TypeCabin, CodeSeason),
    CONSTRAINT Fk_CodeSeason FOREIGN KEY (CodeSeason) REFERENCES season (CodeSeason)
);
    Login or Signup to reply.
  4. 0

    Actually the error message you got already implied that you need to create an index for the referenced column: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. You don’t have to change your code, just create an index for the referenced column in the parent table before attempting to create the child table:

    CREATE TABLE PRICING(
    CodeVillage INT,
    TypeCabin INT,
    CodeSeason CHAR(2),
    WeekPrice INT,
    PRIMARY KEY(CodeVillage, TypeCabin, CodeSeason)
);

create index idx_codeseason on PRICING(CodeSeason);

CREATE TABLE SEASON(
    CodeSeason CHAR(2) PRIMARY KEY,
    NameSeason VARCHAR(20),
    CONSTRAINT Fk_Pricing_CodeSeason Foreign KEY (CodeSeason)
    REFERENCES PRICING(CodeSeason)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search