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.
Reply to discussion (solution)
Why is there no reply to this question? Not a difficult issue.
1. your operating system?
2. what character set does your program use?
3. you should give
A. SQL commands for table creation
B. php code for testing
Hey, come and answer your questions ~~
1. your operating system?
2. what character set does your program use?
3. you should give
A. SQL commands for table creation
B. php code for testing
1. the operating system is MAC OS X
2. the character set is
Mysql> show variables like 'character % ';
+ -------------------------- + -------------------------------------------------------- +
| Variable_name | Value |
+ -------------------------- + -------------------------------------------------------- +
| Character_set_client | latin1 |
| Character_set_connection | latin1 |
| Character_set_database | latin1 |
| Character_set_filesystem | binary |
| Character_set_results | latin1 |
| Character_set_server | latin1 |
| Character_set_system | utf8 |
| Character_sets_dir |/usr/local/mysql-5.1.63-osx10.6-x86_64/share/charsets/|
+ -------------------------- + -------------------------------------------------------- +
8 rows in set (0.08 sec)
The data used to insert the database is transferred from an HTML page encoded as a UTF-8 using the post method to a php file encoded as a UTF-8.
3.
The SQL command for table creation is input on the terminal as follows:
Mysql> create table room (name char (20) not null primary key, doctor char (20) not null) type = innodb;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Mysql> create table guahao-> (name char (20) not null primary key,-> sex char (10) not null,
-> Age char (10) not null,
-> Room char (20) not null,
-> Foreign key (room) references room (name) on delete cascade on update cascade)
-> Type = innodb;
Query OK, 0 rows affected, 1 warning (0.41 sec)
PHP code is
Hospital outpatient registration system
Enter complete patient information!
Query ($ query); if (! $ Result) {echo $ db-> error; echo "login failed! "; Exit;} else {echo"Registration successful!
";}$ Db-> close () ;?> Continue registration
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
Ignore foreign key: SET FOREIGN_KEY_CHECKS = 0
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
I have solved that problem, that is, the character set problem. thank you! So many days to close the post
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
......
Hello, moderator. I used the same table creation command as the landlord to create a table, but cannot insert the same data as the landlord. the prompt foreign constraints fails is displayed. how can I solve this problem?