MySQL FOREIGN KEY constraint

MySQL cluster creates a foreign key, divided into four constraints: no action,restrict,cascade,set null. If Table A's primary key is a field in table B, the field is called the foreign key of B, table A is called the primary table, and table B is called from the table. Foreign keys are used to implement referential integrity, and different foreign key constraints will allow the two tables to be tightly combined, especially if the modified or deleted cascade operation will make routine maintenance easier.

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

· Set NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null.

· No action: In the ANSI SQL-92 standard, no action means this does not take action, that is, if there is an associated foreign key value in the referenced table, the attempt to delete or update the primary key value is not allowed (Gruber, mastering SQL, 2000:181). InnoDB rejects the delete or update operation on the parent table.

Resrict: Same as no action.

Environment Simulation:

First, add two tables: Ta_resource and Ta_resourcetime.

DROP TABLE IF EXISTS ' Ta_resource '; CREATE TABLE ' Ta_resource ' (' ResourceId ' varchar) NOT NULL, ' resourcepid ' varchar (+) DEFAULT NULL, ' resourcename ' varchar (+) default NULL, ' Type ' varchar (+) default NULL, ' DESC ' varchar (+) default NULL, ' Priority ' smallint (6) DEF Ault null, ' isfold ' varchar (2) default null, ' Isuse ' varchar (2) default null, ' URL ' varchar (+) default NULL, ' Icon ' V Archar (+) default NULL, ' CSS ' varchar (+) default NULL, ' JavaScript ' varchar (+) default NULL, ' TimeSpan ' datetime DE FAULT 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) NOT NULL, ' ResourceId ' varchar ($) NOT null, ' resourcename ' Varch AR (+) default NULL, ' Roleid ' varchar (+) NOT null, ' RoleName ' varchar (+) default NULL, ' URL ' varchar (+) default NULL, ' StartTime ' datetime NOT NULL, ' EndTime ' datetime NOT NULL, ' IsDeleted ' varchar (2) Default NULL, ' Desc ' varchar (+) DEFAULT NULL, PRIMARY key (' Timeid '), key ' ResourceId ' (' ResourceId '), CONSTRAINT ' Resource Id ' FOREIGN KEY (' ResourceId ') REFERENCES ' Ta_resource ' (' ResourceId ') on the DELETE no action on UPDATE no action) engine=ndb Cluster DEFAULT Charset=utf8;

1. No action

The foreign keys in both tables use the on DELETE no action on the UPDATE no action

If you change the data, Ta_resource is the primary table, and Ta_resourcetime is from the table.

When updating data: You can modify the primary table, but you cannot modify the data from the table.

When deleting data: Delete the primary table from the table before deleting it.

2. When Cascade

When the cluster is built and then inserted into the database table, you encounter the problem when both on delete and on update use CASCADE: You cannot add a foreign key constraint.

Previously thought is the MySQL cluster does not support foreign keys, on-line check MySQL cluster This version supports foreign KEY constraints and brother Siang discussed this issue, indicating that the MySQL cluster supports foreign key constraints. So after many practices, find the cause of this problem: the MySQL cluster supports the use of cascade, but on delete and on update can only have one use cascade, the other uses no action.

3. Insufficient memory

Problem: Insert not into the data, after query, is due to insufficient memory

Workaround:

Set the memory in the configuration file Config.ini, which is mainly set up for these items:

maxnooftables=10240

Set the maximum number of table objects in a cluster
maxnoofattributes=500000

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

maxnoofconcurrenttransactions=1000000
Used to set the number of possible concurrent transactions within a node
maxnoofconcurrentoperations=1000000
Set the number of records that can appear simultaneously in the update phase or locked simultaneously
maxnooforderedindexes=10240
Set the total number of ordered indexes

If you do not configure these, the MySQL cluster will default to the size of the memory, when the memory is full after the data will not be plugged in.

MySQL FOREIGN KEY constraint