Shulou(Shulou.com)05/31 Report--

This article mainly introduces what matters needing attention in the use of foreign keys in Mysql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

Foreign key, FOREIGN KEY, this thing, as DBA, in Oracle, we do not recommend implementing constraints at the database level, because its maintenance cost is very high.

For example, you have to ensure the index, you have to ensure the order when importing data, etc., so we recommend that the application control the logic.

It is even less recommended in MYSQL, but here we mainly talk about the problems that should be paid attention to in the process of use. [@ more@] # # create constraints, pay attention to the naming convention FK1,FK2,FK3. If you do not specify a constraint name, one is automatically created.

Create table......

Constraint `FK1` foreign key (`FKid`) REFERENCES `user` (`id`)

ON DELETE CASCADE ON UPDATE CASCADE

# # corresponding fields (foreign key and the referenced key)

Corresponding columns in the foreign key and the referenced key

> > must have the same internal data type

Must have similar internal data types inside InnoDB so that they can be compared without a type conversion.

> > the data length of integer fields must be the same.

The size and sign of integer types must be the same.

The length of the character can be different.

The length of string types need not be the same. For non-binary (character) string columns

> > non-binary character fields, the character set and collation must also be the same

For non-binary (character) string columns, the character set and collation must be the same.

# # if an INNODB table has a foreign key, it will not be able to directly change the storage engine unless the foreign key is deleted.

If an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first

=

Root@127.0.0.1: test 12:21:05 > alter table audit engine=myisam

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Root@127.0.0.1: test 12:21:06 >

Root@127.0.0.1: test 12:25:40 > alter table audit drop foreign key FK1

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

Root@127.0.0.1: test 12:25:46 > alter table audit engine=myisam

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

=

# # set FOREIGN_KEY_CHECKS = 0.

Tables can be imported without dependencies; that's what mysqldump does.

This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded

# # to delete a constraint, specify the correct constraint name

Create table user (id int, username varchar (20), primary key (id)) engine=innodb

Create table audit (id int, user_id int, primary key (id))

Constraint foreign key (`user_ id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) engine=innodb

Insert into user values (1); insert into audit values (1)

=

Root@127.0.0.1: test 11:00:19 > alter table audit drop FOREIGN KEY user_id

ERROR 1025 (HY000): Error on rename of'. / test/audit' to'. / test/#sql2-4847murc'(errno: 152)

# Why is there an error here?

Root@127.0.0.1: test 11:00:19 > show innodb status G

LATEST FOREIGN KEY ERROR

-

100202 11:00:30 Error in dropping of a foreign key constraint of table test/audit

In SQL command

Alter table audit drop FOREIGN KEY user_id

Cannot find a constraint with the given id user_id.

# the system prompts that you have specified a wrong CONSTRAINT_NAME

Root@127.0.0.1: test 11:57:02 > show create table audit G

* * 1. Row *

Table: audit

Create Table: CREATE TABLE `audit` (

`id`int (11) NOT NULL default'0'

`user_ id` int (11) default NULL

PRIMARY KEY (`id`)

KEY `user_ id` (`user_ id`)

CONSTRAINT `audit_ibfk_ 1` FOREIGN KEY (`user_ id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

# We see that the foreign key names automatically generated by the system are not simple field names.

Root@127.0.0.1: test 11:54:26 > alter table audit drop FOREIGN KEY `audit_ibfk_ 1`

Query OK, 1 row affected (0.21 sec)

Records: 1 Duplicates: 0 Warnings: 0

Thank you for reading this article carefully. I hope the article "what are the precautions for the use of foreign keys in Mysql" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.