Error setting MySQL Foreign key.

There are two tables.

If the parent table has two pk, the creation of the child table fails.

Is there a way?

For no in the parent table, this is a formal sequence value. Do I have to abandon the sequence value of the sequence?

CREATE TABLE parent
(
  no INT AUTO_INCREMENT NOT NULL ,
  id INT NOT NULL,
  PRIMARY KEY(no, id)
) ENGINE = InnoDB;

CREATE TABLE child
(
  id INT NOT NULL,
  parent_id INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(parent_id) REFERENCES parent(id)
) ENGINE = InnoDB;

mysql foreign-keys

2022-09-22 15:59

1 Answers

If you refer to a table with a composite key (a key composed of multiple columns) as a primary key, you must refer to all of those composite keys.

You can fix it as follows:

CREATE TABLE child
(
  id INT NOT NULL,
  parent_no INT NOT NULL,
  parent_id INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(parent_no,parent_id) REFERENCES parent(no,id)
) ENGINE = InnoDB;

Add parent_no INT NOT NULL Please change the reference Constraint to FOREIGN KEY(parent_no,parent_id) REFERENCES parent(no,id).

If child record does not refer to one parent record, If you want to refer only to a particular partial key (part of a compound key), you must generate a key (or index) for the partial key.

CREATE TABLE parent
(
  no INT AUTO_INCREMENT NOT NULL ,
  id INT NOT NULL,
  PRIMARY KEY(no, id),
  INDEX idx_parent_id(id)
) ENGINE = InnoDB;

CREATE TABLE parent
(
  no INT AUTO_INCREMENT NOT NULL ,
  id INT NOT NULL,
  PRIMARY KEY(no, id)
) ENGINE = InnoDB;
CREATE INDEX idx_parent_id ON parent(id);

You can create a unique index (key) for the id of the parent table as above, and then create it like the child table after you asked.

CREATE TABLE child
(
  id INT NOT NULL,
  parent_id INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(parent_id) REFERENCES parent(id)
) ENGINE = InnoDB;

2022-09-22 15:59

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656