Error 150 create table sql

Question

Error 150 create table sql

Navigation

#1 by (2 votes)

1

When creating the following table I get error 150: "Foreign key constraint is incorrectly formed"

    create table seproduce (
    año_s   INT   NOT NULL, 
    mes_s   varchar(30)   NOT NULL, 
    dia_s   INT   NOT NULL, 
    id_producto     INT  NOT NULL, 
    cantidad_1   INT  NOT NULL,
    PRIMARY KEY(año_s, mes_s, dia_s, id_producto),
    FOREIGN KEY(año_s)  REFERENCES fecha(año_s),
    FOREIGN KEY(mes_s)  REFERENCES fecha(mes_s),
    FOREIGN KEY(dia_s)  REFERENCES fecha(dia_s),
    FOREIGN KEY (id_producto) REFERENCES producto(id_producto)
);

The tables to which it refers are the following

create table producto (
id_producto    INT  PRIMARY KEY NOT NULL,
nombre_p    varchar(30)        NOT NULL,
tamaño      INT  NOT NULL,
sabor  varchar(30)   NOT NULL
);

create table periodo (
año_sem    INT  PRIMARY KEY NOT NULL,
gasto_per    INT       NOT NULL,
sueldos_per      INT  NOT NULL
);

create table fecha (
año_s   INT   NOT NULL,
mes_s   varchar(30) NOT NULL,
dia_s   INT NOT NULL,
PRIMARY KEY(año_s, mes_s, dia_s),
año_sem  INT    NOT NULL, 
FOREIGN KEY(año_sem)  REFERENCES periodo(año_sem)
);

Thanks in advance!

mysql

asked by Javier Russell Richards Valenz 25.05.2017 в 02:28

source

1 answer

How to add value to an empty space arrangement (PHP)? @login_required in view-based classes

score 2 · Accepted Answer

If you have here:

create table fecha (
año_s   INT   NOT NULL,
mes_s   varchar(30) NOT NULL,
dia_s   INT NOT NULL,
PRIMARY KEY(año_s, mes_s, dia_s),
año_sem  INT    NOT NULL, 
FOREIGN KEY(año_sem)  REFERENCES periodo(año_sem)
);

You make a syntax error in the fecha table, since you create the año_sem column after the CONSTRAINT PRIMARY KEY. In the type of syntax you are using you must first specify the columns and then the rest.

I refer to these lines:

PRIMARY KEY(año_s, mes_s, dia_s), año_sem INT NOT NULL,

as año_sem INT NOT NULL appears after the PRIMARY KEY CONSTRAINT the handler shrieks, because you must first create all the columns and then add the CONSTRAINTS of place.

Then it should be:

create table fecha (
año_s   INT   NOT NULL,
mes_s   varchar(30) NOT NULL,
dia_s   INT NOT NULL,
año_sem  INT    NOT NULL, 
PRIMARY KEY(año_s, mes_s, dia_s),
FOREIGN KEY(año_sem)  REFERENCES periodo(año_sem)
);

Another thing is if you indicate the PRIMARY KEY next to the name of the column, but you can not do that with the fecha table because it has a composite PK. For more details about it you can consult: MySQL Primary Key .

By the way, I would not use ñ for column names. You can use for example annio .

And also, I do not know if in the table fecha the columns año_sem and año_s refer to the same ¿?

EDIT

In the case of the seproduce table, you want to use the (composite) PK of the fecha table as FK in seproduce . You should do it in the following way:

 create table seproduce (
    año_s   INT   NOT NULL, 
    mes_s   varchar(30)   NOT NULL, 
    dia_s   INT   NOT NULL, 
    id_producto     INT  NOT NULL, 
    cantidad_1   INT  NOT NULL,
    PRIMARY KEY(año_s, mes_s, dia_s, id_producto),
    FOREIGN KEY(año_s,mes_s,dia_s)  REFERENCES fecha(año_s,mes_s,dia_s),
--  FOREIGN KEY(año_s)  REFERENCES fecha(año_s),
--  FOREIGN KEY(mes_s)  REFERENCES fecha(mes_s),
--  FOREIGN KEY(dia_s)  REFERENCES fecha(dia_s),
    FOREIGN KEY (id_producto) REFERENCES producto(id_producto)
 );