if reforeign key is in itself

Question

if the foreign key is in itself table, how to handle the first insertion problem.

 /*外键是自己本身该如何处理插入问题*/
  create table if not exists Course(
     Cno varchar(10) primary key,
     Cname varchar(100) not null,
     Cpno varchar(10),
     Ccredit tinyint,

     foreign key (cpno) references course(cno)
);
/* the under sql will across error */
insert into Course(cno,cname,cpno,ccredit) value("1","数据库","5",4);
insert into Course(cno,cname,cpno,ccredit) value("2","数学",null,2);
insert into Course(cno,cname,cpno,ccredit) value("3","信息系统","1",4);
insert into Course(cno,cname,cpno,ccredit) value("4","操作系统","6",3);
insert into Course(cno,cname,cpno,ccredit) value("5","数据结构","7",4);
insert into Course(cno,cname,cpno,ccredit) value("6","数据处理",null,2);
insert into Course(cno,cname,cpno,ccredit) value("7","PASCAL语言","6",4);

enter image description here how can I initialize the table course with Mysql?

O. Jones · Answer 1 · 2017-11-26T13:14:52.527

Your first INSERT is this:

insert into Course(cno,cname,cpno,ccredit) value("1","数据库","5",4);

This attempts to create a row with value 5 in the column defined as the foreign key (FK), but without ensuring that a row with a cno value of 5 already exists. The FK constraint therefore refuses the insert.

You can fix this in one of three ways.

First, insert your rows in an order that insures the cno values exist before you use them by referring to them in cpno. I think this will be:

2, 6, 7, 5, 1, 3, 4

where you work out the order with a graph-traversal algorithm starting with root rows (rows with null cpno values).

Second, try turning off foreign key checking by giving this command SET FOREIGN_KEY_CHECKS=0; before your inserts. Give this command after your inserts to re-enable checking. SET FOREIGN_KEY_CHECKS=1;

Third, drop the foreign key constraint from the table before doing your inserts. Then add it back when you're done.

The second two methods generally apply only to bulk insertion operations you do on a quiet system. You probably don't want to disable foreign key checking in production, because it has value for data integrity assurance.

Read this. How to temporarily disable a foreign key constraint in MySQL?

if reforeign key is in itself

1 Answers1