Error creating a table in mysql

Question

Error creating a table in mysql

Navigation

#1 by (0 votes)

0

CREATE TABLE EPI (
  CODIGO__EPI INTEGER NOT NULL, 
  USUARIO_CODIGO_USUARIO INTEGER NOT NULL, 
  TIPO_EPI_CODIGO_TIPO_EPI INTEGER NOT NULL, 
  EPC_EPI VARCHAR (255) NOT NULL, 
  DATA_CADASTRO_EPI DATE NOT NULL, 
  VALIDADE_EPI DATE NOT NULL, 
  VALIDADE_CA DATE NOT NULL, 
  PRIMARY KEY (CODIGO__EPI), 
  FOREIGN KEY (TIPO_EPI_CODIGO_TIPO_EPI) 
    REFERENCES TIPO_EPI (CODIGO_TIPO_EPI) 
      ON DELETE NO ACTION ON UPDATE NO ACTION, 
  FOREIGN KEY (USUARIO_CODIGO_USUARIO) 
    REFERENCES USUARIO (CODIGO_USUARIO) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
);

INSERT INTO EPI (CODIGO__EPI, USUARIO_CODIGO_USUARIO, TIPO_EPI_CODIGO_TIPO_EPI, EPC_EPI, DATA_CADASTRO_EPI, VALIDADE_EPI, VALIDADE_CA) 
  VALUES  (1, 2, 1, 'H771000000000', '2017-03-03', '2018-01-20', '2018-05-20'),
          (2, 2, 2, 'H0C210546072055002202295F', '2017-03-03', '2017-12-03', '2018-05-20'),
          (3, 4, 5, 'HABCD00000000000000000000', '2017-04-03', '2017-11-06', '2018-05-20'),
          (4, 3, 3, 'HAD8A200018BACD9013000068', '2017-05-03', '2018-01-22', '2018-05-20'),
          (5, 2, 4, 'HABCDABCDABCDBACDABCDABCD', '2017-03-03', '2018-01-20', '2018-05-20'),
          (6, 2, 1, 'H4', '2017-11-17', '2017-11-22', '2018-05-20'),
          (7, 3, 1, 'H5', '2017-11-17', '2017-11-22', '2018-05-20'),
          (8, 4, 1, 'H6', '2017-11-17', '2017-12-20', '2018-05-20'),
          (9, 5, 1, 'H7', '2017-11-17', '2017-12-20', '2018-05-20'),
          (10, 2, 6, 'H8', '2017-11-17', '2017-11-16', '2018-05-20'),
          (11, 2, 6, 'H9', '2017-11-17', '2017-11-16', '2018-05-20'),
          (12, 2, 2, 'H10', '2017-11-17', '2018-01-20', '2018-05-20'),
          (13, 3, 2, 'H11', '2017-11-17', '2018-01-20', '2018-05-20'),
          (14, 4, 2, 'H12', '2017-11-17', '2018-01-20', '2018-05-20'),
          (15, 4, 1, 'H13', '2017-11-17', '2018-01-20', '2018-05-20'),
          (16, 2, 3, 'H14', '2017-11-17', '2018-01-20', '2018-05-20'),
          (17, 2, 4, 'H15', '2017-11-17', '2018-01-20', '2018-05-20'),
          (18, 2, 5, 'H16', '2017-11-17', '2018-01-20', '2018-05-20'),
          (19, 5, 6, 'H17', '2017-11-17', '2018-01-20', '2018-05-20'),
          (20, 5, 2, 'H18', '2017-11-17', '2018-01-20', '2018-05-20'),
          (21, 4, 4, 'H19', '2017-11-17', '2018-01-20', '2018-05-20'),
          (22, 3, 3, 'H20', '2017-11-17', '2018-01-20', '2018-05-20'),
          (23, 2, 6, 'H2', '2017-11-17', '2017-11-16', '2018-05-20'),
          (24, 5, 6, 'H1', '2017-11-17', '2017-11-16', '2018-05-20'),
          (25, 2, 1, 'H4321', '2017-03-03', '2018-01-20', '2018-05-20'),
          (26, 2, 3, 'H300833B2DDD9014000000000', '2017-11-17', '2018-01-20', '2018-05-20');

-- Index: EPI_FKIndex1
CREATE INDEX EPI_FKIndex1 ON EPI (TIPO_EPI_CODIGO_TIPO_EPI);

-- Index: EPI_FKIndex2
CREATE INDEX EPI_FKIndex2 ON EPI (USUARIO_CODIGO_USUARIO);

Error :

# 1005 - Can not create table 'inspeasy'. 'epi' (error # 150 'Foreign key constraint is incorrectly formed')

mysql sqlite3

asked by anonymous 04.12.2017 / 20:31

1 answer

SQL / LINQ vs. Data to feed graphic How can I make an access control visually in ruby on rails?

score 0 · Accepted Answer

Put the foreign keys creation instruction separate from the table creation:

CREATE TABLE EPI (
  CODIGO__EPI INTEGER NOT NULL PRIMARY KEY, 
  USUARIO_CODIGO_USUARIO INTEGER NOT NULL, 
  TIPO_EPI_CODIGO_TIPO_EPI INTEGER NOT NULL, 
  EPC_EPI VARCHAR (255) NOT NULL, 
  DATA_CADASTRO_EPI DATE NOT NULL, 
  VALIDADE_EPI DATE NOT NULL, 
  VALIDADE_CA DATE NOT NULL 


);

 ALTER TABLE EPI ADD CONSTRAINT FK_EPI_1 FOREIGN KEY (TIPO_EPI_CODIGO_TIPO_EPI) REFERENCES TIPO_EPI (CODIGO_TIPO_EPI); 

 ALTER TABLE EPI ADD CONSTRAINT FK_EPI_2 FOREIGN KEY (USUARIO_CODIGO_USUARIO) REFERENCES USUARIO (CODIGO_USUARIO);