Mysql's Cascade / Restrict / No action

2011-01-12  来源:本站原创  分类:Database  人气:164 

Child table foreign key constraint meaning:

If the candidate is not found in the parent table key is not allowed on the child table insert / update

Foreign key constraints on the meaning of the parent table:

On the parent table update / delete to update or delete the child table has one or more matching rows corresponding to the candidate key, depending on the behavior of the parent table

: In the definition of child table foreign key specified on update / on delete clauses, InnoDB supports five kinds of ways, as follows

. Cascade mode

In the parent table update / delete records, synchronization update / delete records out of the matching sub-table

On delete cascade starting from the mysql3.23.50 available; on update cascade starting from the mysql4.0.8 available

. Set null method

In the parent table update / delete records, it will match the records on child table column is set to null

Pay attention to the child table foreign key column can not be not null

On delete set null start from mysql3.23.50 available; on update set null is available from the beginning mysql4.0.8

. No action mode

If the child table has matching records, then the candidate is not allowed on the keys corresponding to the parent table update / delete operations

This is the ANSI SQL-92 standard, began to support from the mysql4.0.8

. Restrict method

With no action, foreign key constraints are checked immediately

. Set default mode

Parser know this action, but Innodb does not recognize, do not know what that means. . .

Note: trigger cascade behavior is not affected by the impact of foreign keys, that will not trigger Solutions Development

In mysql, the contrary with the SQL standard three-point

1. If the parent table there are multiple key values are the same, then conducting the foreign key check, it will have the same key values as the other lines do not exist; than

For example, when defining a foreign key restrict behavior, a child table row that corresponds to more than one parent table row (with the same key value), Innodb is not allowed to delete the parent table

All these lines on the

2. Son table is the same table, self-reference is not allowed when specified on update cascade, on update set null

From mysql4.0.13 start, allowing the same table on delete set null on

From mysql4.0.21 start, allowing the same table on delete cascade on

But the cascade can not exceed the 15 level

3, Innodb checking unique, constraint constraint is row by row rather than the end of the statement or transaction;

SQL standard of constraint checks are completed in the statement is executed

相关文章
  • Mysql's Cascade / Restrict / No action 2011-01-12

    Child table foreign key constraint meaning: If the candidate is not found in the parent table key is not allowed on the child table insert / update Foreign key constraints on the meaning of the parent table: On the parent table update / delete to upd

  • mysql garbled cascade problem 2011-09-15

    Generally multi-table insert data with the use of such time, a record of user information table, another table record corresponding to the user permissions, two tables are used to associate the user ID of the table You add new users when certain one-

  • mysql delete cascade 2010-11-06

    create table a ( id varchar(20) primary key, password varchar(20) not null ) create table b ( id int identity(1,1) primary key, name varchar(50) not null, userId varchar(20), foreign key (userId) references a(id) on delete cascade ) Table b has creat

  • mysql foreign key to achieve the use of cascade delete. Update - Database cascade delete 2010-04-21

    MySQL foreign key to achieve the use of cascading delete, update MySQL storage engines support foreign keys only InnoDB, create a foreign key in the time required the parent table must have a corresponding index, the child table when you create a for

  • mysql之外键 2014-12-30

    保持数据一致性,完整性,主要目的是控制存储在外键表中的数据. 使两张表形成关联,外键只能引用外表中的列的值 1 父表和子表需要使用相同的存储引擎,且只能为innodb. 2 外键列和参照列必须具有相似的数据类型,其中数字的长度或是否有符号位必须相同,字符的长度可以不同. 3 外键列和参照列必须创建索引,如果外键列不存在索引,mysql将自动创建. on delete|update cascade 从父表删除或更新且自动删除或更新子表中匹配的行 set null 从父表删除或更新行,并设置子表中的

  • Under windows build environment for Apache + MySql + Php 2010-07-12

    First, the environment WindowsXP Apache http://httpd.apache.org/download MySql http://dev.mysql.com/downloads/mysql/ PHP http://www.php.net/downloads.php I downloaded the relevant versions were: apache_2.2.4-win32-x86-no_ssl.zip, mysql-essential-5.1.

  • MySQL binary logs on the understanding Binlog 2010-10-28

    MySQL, MySQL binary log can be said that the most important or log, it records all the DDL and DML (in addition to the data query) statement, to form an event record also contains statements by the execution time consumed, MySQL binary logs is error-

  • 聊天室php&mysql(五) 2014-03-06

    第五个页面name="changenick.php" <? include "../signup/mysql.php"; //echo $userid; if($action=="ok") { $sql="select * from chat_user_list where userid='$nick'"; $result=mysql_query($sql) or die(mysql_error()); if(!$res

  • MySQL系列之一键安装脚本----单实例/多实例 2015-05-09

    最近在搞MySQL,由于经常测试一些东西.因此,就写了一个一键安装脚本. 脚本用途: 用于在CentOS/RHEL 6.x系统上快速部署出Mysql的单实例或者多实例环境 脚本说明: 该脚本运行情况良好 针对脚本中,每一步命令执行的正误判断以及提醒非常醒目,可协助执行者快速定位错误源 脚本诸多内容都以声明变量,增加了脚本的灵活性和扩展性 脚本以做模块化处理,对应功能对应函数,方便SA快速更改和了解该脚本 该脚本使用注意事项: 1.能够通公网或者mysql源码包已经放置到/usr/local/sr

  • FOREIGN KEY Constraints 2011-05-06

    InnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this: [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_

  • Database Management Chapter VI 2010-12-29

    Monitoring of the DB DBMS, called database management, something also known as database protection. The management of the database achieved primarily through four: database recovery, concurrency control, integrity control and safety control. DBS oper

  • SQLite Foreign Key Support - sqlite3 .6.19 - from sqlite.org 2011-07-30

    Table Of Contents 1. Introduction to Foreign Key Constraints 2. Enabling Foreign Key Support 3. Required and Suggested Database Indexes 4. Advanced Foreign Key Constraint Features 4.1. Composite Foreign Key Constraints 4.2. Deferred Foreign Key Const

  • DB2 Personal Summary 2011-04-07

    DB2 authentication In the database manager configuration (DBM CFG) file using the AUTHENTICATION parameter setting authentication, DBM CFG file is an instance-level configuration file. Therefore, AUTHENTICATION parameters affect all databases in this

  • PostgreSQL的外键深入使用 2013-06-01

    有开发同事问及postgresql外键的用法,这里普及一下.外键是一个很基础的概念,使用得当可以对事务的一致性有很好的保障,方法上和Oracle是很接近的,作用很简单地说就是保证子表的数据都能在主表中找到,可保证数据一致性. 建立主表 postgres=# create table t_parent( postgres(# id serial primary key, postgres(# vname varchar(32), postgres(# ctime timestamp without

  • 2000个软件开发领域的高频特殊词及精选例句(一) 2015-03-19

    superword是一个Java实现的英文单词分析软件,主要研究英语单词音近形似转化规律.前缀后缀规律.词之间的相似性规律等等. 1.单词 hadoop 的匹配文本: Subash D'Souza is a professional software developer with strong expertise in crunching big data using Hadoop/HBase with Hive/Pig. Apache Flume Distributed Log Collect

  • 2000个软件开发领域的高频特殊词及精选例句(二) 2015-03-19

    superword是一个Java实现的英文单词分析软件,主要研究英语单词音近形似转化规律.前缀后缀规律.词之间的相似性规律等等. 276.单词 tokenizer 的匹配文本: Analysis is done by the analyzer, which is built of tokenizer and zero or more filters, and can also have zero or more character mappers. Mastering ElasticSearch

  • A small set of SQL commands 2009-02-08

    A small set of SQL commands 1, data manipulation language (DML: select, delete, insert, update) "1" to query the database directory: db2 list db directory "2" to query the database table db2 list tables the current user db2 list tables

  • Small set of DB2 SQL commands 2010-03-10

    1, data manipulation language (DML: select, delete, insert, update) <1> check the database directory: db2 list db directory <2> query the database table db2 list tables the current user db2 list tables for all for all tables db2 list tables fo

  • DB2 SQL commands used 2010-03-20

    1, data manipulation language (DML: select, delete, insert, update) <1> check the database directory: db2 list db directory <2> query the database table db2 list tables the current user db2 list tables for all for all tables db2 list tables fo

  • Small set of DB2 SQL commands to switch 2010-03-23

    1, data manipulation language (DML: select, delete, insert, update) <1> check the database directory: db2 list db directory <2> query the database table db2 list tables the current user db2 list tables for all for all tables db2 list tables fo