오류 1452 : 하위 행을 추가하거나 업데이트 할 수 없습니다. 외래 키 제약 조건이 실패합니다.

오류 1452 : 하위 행을 추가하거나 업데이트 할 수 없습니다. 외래 키 제약 조건이 실패합니다.

---

아래와 같이 MySQL Workbench에서 테이블을 만들었습니다.

ORDRE 테이블 :

CREATE TABLE Ordre (
  OrdreID   INT NOT NULL,
  OrdreDato DATE DEFAULT NULL,
  KundeID   INT  DEFAULT NULL,
  CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID),
  CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES Kunde (KundeID)
)
  ENGINE = InnoDB;

PRODUKT 테이블 :

CREATE TABLE Produkt (
  ProduktID          INT NOT NULL,
  ProduktBeskrivelse VARCHAR(100) DEFAULT NULL,
  ProduktFarge       VARCHAR(20)  DEFAULT NULL,
  Enhetpris          INT          DEFAULT NULL,
  CONSTRAINT Produkt_pk PRIMARY KEY (ProduktID)
)
  ENGINE = InnoDB;

및 ORDRELINJE 테이블 :

CREATE TABLE Ordrelinje (
  Ordre         INT NOT NULL,
  Produkt       INT NOT NULL,
  AntallBestilt INT DEFAULT NULL,
  CONSTRAINT Ordrelinje_pk PRIMARY KEY (Ordre, Produkt),
  CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID),
  CONSTRAINT Ordrelinje_fk1 FOREIGN KEY (Produkt) REFERENCES Produkt (ProduktID)
)
  ENGINE = InnoDB;

그래서 ORDRELINJE테이블에 값을 삽입하려고 할 때 다음을 얻습니다.

오류 코드 : 1452. 하위 행을 추가하거나 업데이트 할 수 없습니다. 외래 키 제약 조건이 실패했습니다 ( srdjank. Ordrelinje, CONSTRAINT Ordrelinje_fkFOREIGN KEY ( Ordre) REFERENCES Ordre( OrdreID)).

이 주제에 대한 다른 게시물을 보았지만 운이 없습니다. 내가 무엇을 감독하고 있거나 무엇을해야할지 생각하고 있습니까?

---

에서 촬영 FOREIGN KEY 제약 조건을 사용하여

외래 키 관계에는 중앙 데이터 값을 보유하는 상위 테이블과 상위를 다시 가리키는 동일한 값이있는 하위 테이블이 포함됩니다. FOREIGN KEY 절은 하위 테이블에 지정됩니다.

상위 테이블에 일치하는 후보 키 값이없는 경우 하위 테이블에 외래 키 값을 생성하려는 모든 INSERT 또는 UPDATE 작업이 거부됩니다.

따라서 오류는 Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails본질적으로 Ordrelinje테이블에 일치하는 행 (OrderID)이없는 테이블에 행을 추가하려고한다는 것을 의미 Ordre합니다.

먼저 Ordre테이블에 행을 삽입해야 합니다.

---

ordre 테이블에 삽입 명령에서 제공된 참조 주문 ID가 없기 때문에이 제약 조건 검사를 받게됩니다.

ordrelinje에 값을 삽입하려면 먼저 ordre 테이블에 값을 입력하고 orderlinje 테이블에 동일한 ordreID를 사용해야합니다.

또는 not null 제약 조건을 제거하고 NULL 값을 삽입 할 수 있습니다.

---

You must delete data in the child table which does not have any corresponding foreign key value to the parent table primary key .Or delete all data from the child table then insert new data having the same foreign key value as the primary key in the parent table . That should work . Here also a youtube video

---

This error generally occurs because we have some values in the referencing field of the child table, which do not exist in the referenced/candidate field of the parent table.

Sometimes, we may get this error when we are applying Foreign Key constraints to existing table(s), having data in them already. Some of the other answers are suggesting to delete the data completely from child table, and then apply the constraint. However, this is not an option when we already have working/production data in the child table. In most scenarios, we will need to update the data in the child table (instead of deleting them).

Now, we can utilize Left Join to find all those rows in the child table, which does not have matching values in the parent table. Following query (missing from other answers), would be helpful to fetch those non-matching rows:

SELECT child_table.* 
FROM child_table 
LEFT JOIN parent_table 
  ON parent_table.referenced_column = child_table.referencing_column 
WHERE parent_table.referenced_column IS NULL

Now, you can generally do one (or more) of the following steps to fix the data.

1. Based on your "business logic", you will need to update/match these unmatching value(s), with the existing values in the parent table. You may sometimes need to set them null as well.
2. Delete these rows having unmatching values.
3. Add new rows in your parent table, corresponding to the unmatching values in the child table.

Once the data is fixed, we can apply the Foreign key constraint using ALTER TABLE syntax.

---

The Problem is with FOREIGN KEY Constraint. By Default (SET FOREIGN_KEY_CHECKS = 1). FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables. MySQL - SET FOREIGN_KEY_CHECKS

We can set foreign key check as disable before running Query. Disable Foreign key.

Execute one of these lines before running your query, then you can run your query successfully. :)

1) For Session (recommended)

SET FOREIGN_KEY_CHECKS=0;

2) Globally

SET GLOBAL FOREIGN_KEY_CHECKS=0;

---

This can be fixed by inserting the respective records in the Parent table first and then we can insert records in the Child table's respective column. Also check the data type and size of the column. It should be same as the parent table column,even the engine and collation should also be the same. TRY THIS! This is how I solved mine. Correct me if am wrong.

---

in the foreign key table has a value that is not owned in the primary key table that will be related, so you must delete all data first / adjust the value of your foreign key table according to the value that is in your primary key

---

While inserting the foreign key attribute values, first verify the attributes type, as well as primary key attribute value in the parent relation, if the values in parent relation matches, then you can easily insert/update child attribute values.

---

you should add data from REFERENCES KEY in PRIMARY TABLE to FOREIGN KEY in CHILD TABLE
it means do not add random data to foreign key ، just use data from primary key that is accessable

description of data in foreign key

---

Your ORDRELINJE table is linked with ORDER table using a foreign key constraint constraint Ordrelinje_fk foreign key(Ordre) references Ordre(OrdreID) according to which Ordre int NOT NULL, column of table ORDRELINJE must match any Ordre int NOT NULL, column of ORDER table.

Now what is happening here is, when you are inserting new row into ORDRELINJE table, according to fk constraint Ordrelinje_fk it is checking ORDER table if the OrdreID is present or not and as it is not matching with any OrderId, Compiler is complaining for foreign key violation. This is the reason you are getting this error.

Foreign key is the primary key of the other table which you use in any table to link between both. This key is bound by the foreign key constraint which you specify while creating the table. Any operation on the data must not violate this constraint. Violation of this constraint may result in errors like this.

Hope I made it clear.

---

you should insert at least one raw in each tables (the ones you want the foreign keys pointing at) then you can insert or update the values of the foreign keys

참고URL : https://stackoverflow.com/questions/21659691/error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails