Mysql foreign key constraint _ MySQL

Create a foreign key for a Mysql Cluster. There are four constraints: noaction, restrict, cascade, and setnull. If the primary keyword of Table A is A field in Table B, this field is called the foreign key of Table B. Table A is called the primary table, and Table B is called the slave table. The foreign key is used to achieve the integrity of the reference, different Create a foreign key for a Mysql Cluster. There are four constraints: no action, restrict, cascade, and set null.If the primary keyword of Table A is A field in Table B, this field is called the foreign key of Table B. Table A is called the primary table, and Table B is called the slave table. Foreign keys are used to achieve the integrity of the reference. different foreign keys have different foreign key constraints that can closely combine the two tables, in particular, modifying or deleting cascade operations makes daily maintenance easier.

CASCADE: deletes or updates matched rows from the parent table and automatically deletes or updates the matched rows in the child table.

· Set null: delete or update rows from the parent table, and SET the foreign key column in the child table to NULL.

· No action: in ANSI SQL-92 standards, no action means this does not take ACTION, that is, if there is a related foreign key value in the referenced table, attempts to delete or update key values are not allowed (Gruber, master SQL, 2000: 181 ). InnoDB rejects the deletion or update operation on the parent table.

RESRICT: same as no action.

Environment Simulation:

Add two tables: ta_resource and ta_resourcetime.

DROP TABLE IF EXISTS `ta_resource`;CREATE TABLE `ta_resource` (  `ResourceId` varchar(64) NOT NULL,  `ResourcePId` varchar(64) DEFAULT NULL,  `ResourceName` varchar(64) DEFAULT NULL,  `Type` varchar(64) DEFAULT NULL,  `Desc` varchar(512) DEFAULT NULL,  `Priority` smallint(6) DEFAULT NULL,  `IsFold` varchar(2) DEFAULT NULL,  `IsUse` varchar(2) DEFAULT NULL,  `URL` varchar(64) DEFAULT NULL,  `Icon` varchar(512) DEFAULT NULL,  `Css` varchar(64) DEFAULT NULL,  `JavaScript` varchar(64) DEFAULT NULL,  `TimeSpan` datetime DEFAULT NULL,  `IsDefault` varchar(2) DEFAULT NULL,  PRIMARY KEY (`ResourceId`)) ENGINE=ndbcluster DEFAULT CHARSET=utf8;-- ------------------------------  Table structure for `ta_resourcetime`-- ----------------------------DROP TABLE IF EXISTS `ta_resourcetime`;CREATE TABLE `ta_resourcetime` (  `TimeId` varchar(64) NOT NULL,  `ResourceId` varchar(64) NOT NULL,  `ResourceName` varchar(64) DEFAULT NULL,  `RoleId` varchar(64) NOT NULL,  `RoleName` varchar(64) DEFAULT NULL,  `URL` varchar(64) DEFAULT NULL,  `StartTime` datetime NOT NULL,  `EndTime` datetime NOT NULL,  `IsDeleted` varchar(2) DEFAULT NULL,  `Desc` varchar(512) DEFAULT NULL,  PRIMARY KEY (`TimeId`),  KEY `ResourceId` (`ResourceId`),  CONSTRAINT `resourceid` FOREIGN KEY (`ResourceId`) REFERENCES `ta_resource` (`ResourceId`) ON   DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

1. no action

The foreign keys in the two tables use the on delete no action on update no action.

If you change the data, ta_resource is the master table, and ta_resourcetime is the slave table.

When updating data: you can modify the master table, but cannot modify the data in the slave table.

When deleting data: first delete the slave table and then the Master table.

2. when cascade

When the cluster is built and inserted into the database table, cascade is used in both on delete and on update: Foreign key constraints cannot be added.

I thought that the mysql Cluster does not support foreign keys. I checked the mysql Cluster online and checked this version to support foreign key constraints. I discussed this issue with my fellow teachers, indicating that the mysql Cluster supports foreign key constraints. After many practices, find out the cause of this problem: cascade is supported in the mysql Cluster, but on delete and on update can only use cascade, and the other can use no action.

3. insufficient memory

Problem: the data cannot be inserted. it is queried because of insufficient memory.

Solution:

In the configuration file config. ini, set the memory, mainly to the following items:

MaxNoOfTables = 10240

Set the maximum number of table objects in the cluster
MaxNoOfAttributes = 500000

Set the number of attributes that can be defined in the cluster

MaxNoOfConcurrentTransactions = 1000000
Used to set the number of possible concurrent transactions in the node
MaxNoOfConcurrentOperations = 1000000
Set the number of records that can appear in the update phase or be locked at the same time
MaxNoOfOrderedIndexes = 10240
Set the total number of ordered indexes

If this is not configured, the mysql Cluster will default the memory size. when the memory is full, data cannot be inserted.

The above is the content of Mysql foreign key constraint _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!