How to insert records into word tables with foreign key constraints? I created two tables in mysql: mysql & gt; describeroom; + -------- + ---------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ---------- How to insert records into word tables with foreign Key constraints?
I have created two tables in mysql.
Mysql> describe room;
+ -------- + ---------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------- + ---------- + ------ + ----- + --------- + ------- +
| Name | char (20) | NO | PRI | NULL |
| Doctor | char (20) | NO | NULL |
+ -------- + ---------- + ------ + ----- + --------- + ------- +
2 rows in set (0.07 sec)
Word table is
Mysql> describe guahao;
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| Name | char (20) | NO | PRI | NULL |
| Sex | char (10) | NO | NULL |
| Age | char (10) | NO | NULL |
| Room | char (20) | NO | MUL | NULL |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
4 rows in set (0.00 sec)
The foreign key is room, and the on delete cascade on update cascade
There is no problem in inserting data into the word table in the command line. The result is:
Mysql> insert into guahao values
-> ('Xiao Zhang', 'male', '30', 'dermatology ');
Query OK, 1 row affected (0.04 sec)
Mysql> select * from guahao;
+ -------- + ----- + ----------- +
| Name | sex | age | room |
+ -------- + ----- + ----------- +
| Wang ba | female | 60 | internal medicine |
| Qian 'er | male | 40 | nake |
| Zhao Yi | male | 20 | wuguanke |
| Sun San | female | 35 | orthopedic department |
| Lonely | male | 22 | orthopedic department |
| Xiao Zhang | male | 30 | dermatology |
| James | male | 25 | brain division |
| Li Si | male | 30 | dermatology |
+ -------- + ----- + ----------- +
9 rows in set (0.00 sec)
However, when I use PHP, the same insert statement will report an error. the error message is as follows:
Cannot add or update a child row: a foreign key constraint fails ('clinic '., CONSTRAINT '# sql-9e_13_ibfk_1' foreign key ('Room ') REFERENCES 'Room' ('name') on delete cascade on update cascade)
Please help me see where the problem is! Sorry! Cried and asked once. The problem was not solved.
------ Solution --------------------
Character set problems!
1. you have not set a character set for the character type field in your table (default: latin1)
2. You did not declare the character set after connecting to the database (the set names command is missing)
3. you said you used the UTF-8 character set, and the program contains charset = GB18030
Actually, the gbk character set is used.
GB18030 is a formal national standard and has not yet been implemented in computers. Therefore, we actually use its subset-GBK