MySQL(Constraint)

2019独角兽企业重金招聘Python工程师标准>>>

MySQL 约束作用：
  约束用于限制加入表的数据的类型

MySQL 约束范围：
  表约束
  列约束(单列约束，多列约束)

MySQL key约束三种
  主键约束(PRIMARY KEY)
  唯一键值约束(UNIQUE KEY)
  外键约束(FOREIGN KEY)
  
MySQL 其他约束:
  非空约束(NOT NULL)
  检查约束(CHECK)
  默认约束(DEFAULT)

主键约束:
  create table actor(f1 int, f2 text, PRIMARY KEY (f1[, f2...]));
  create table actor(f1 int, f2 text, constraint xxx PRIMARY KEY (f1[, f2...]));
  ALTER TABLE actor ADD PRIMARY KEY (f1[, f2...]);
  ALTER TABLE actor ADD constraint xxx PRIMARY KEY (f1[, f2...]);
  
  ALTER TABLE actor drop PRIMARY KEY;

唯一建约束
  create table actor(f1 int unique, f2 text);
  create table actor(f1 int unique, f2 text);
  create table actor(f1 int, f2 text, UNIQUE KEY (f1[, f2...]));
  create table actor(f1 int, f2 text, constraint xxx UNIQUE KEY (f1[, f2...]));
  
  ALTER TABLE actor ADD UNIQUE KEY fname_actor (first_name,actor_id);  
  ALTER TABLE actor ADD constraint xxx UNIQUE KEY fname_actor (first_name,actor_id); 
  CREATE UNIQUE INDEX fname_actor ON actor (first_name,actor_id);

  ALTER TABLE actor drop KEY fname_actor;
  (drop index fname_actor on actor; )

  唯一键值索引类型不能是 FULLTEXT 或者 SPATIAL 类型 
  
外键约束
  create table actor(f1 int, f2 text, FOREIGN KEY (f1[, f2...] REFERENCES ref_table (f1[, f2...)); 
 create table actor(f1 int, f2 text, CONSTRAINT fk_lastname FOREIGN KEY (f1[, f2...] REFERENCES ref_table (f1[, f2...))
  
  ALTER TABLE this_table ADD FOREIGN KEY (field1) REFERENCES ref_table (fieldA);  
 ALTER TABLE this_table ADD CONSTRAINT fk_lastname FOREIGN KEY (last_name) REFERENCES other_table (last_name);  
  CREATE FOREIGN key fname_actor ON actor (first_name,actor_id);

  ALTER TABLE this_table DROP FOREIGN KEY fk_lastname;  

  外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作

  外键约束语法
    [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name, ...)
      [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
      [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

  外键约束语句:
    CREATE TABLE
    ALTER TABLE

  外键约束触发事件:
    ON DELETE、ON UPDATE表示事件触发限制

  外键约束触发限制:
    RESTRICT（限制外表中的外键改动）
    CASCADE（跟随外键改动）
    SET NULL（设空值）
    SET DEFAULT（设默认值）
    NO ACTION（无动作，默认的）

非空约束|可空约束
  create table tablename(f1 int not null, f2 text);
  create table tablename(f1 int, f2 text default null);

  alter table tablename add column f1 int not null;
  alter table tablename add column f2 int default null;

  alter table tablename alter column f1 set not null;
  alter table tablename alter column f2 set default NULL;

  无删除该约束语句

检查约束
  create table tablename(f1 int CHECK(f1>10), f2 int);
  create table tablename(f1 int, f2 int, CHECK (f1>10 and f2>20));
  create table tablename(f1 int, f2 int, CONSTRAINT xxx CHECK (f1>10 and f2>20));

  alter table tablename add CHECK (f1>10);
  alter table tablename add CONSTRAINT xxx CHECK (f1>10 and f2>20);

  alter table tablename drop CHECK xxx;

  MySQL中CHECK约束可以使用，但是是无效的，即MySQL并不会去执行检查
  
默认约束
  create table tablename(f1 int DEFAULT xxx [ON UPDATE yyy], f2 text);

  alter table tablename alter f1 SET DEFAULT xxx;

  alter table tablename alter f1 DROP DEFAULT;

约束个数
MySQL表中只能有一个AUTO_INCREMENT字段，而且这个字段必须被定义为键
MySQL表中能有多个NULL字段
MySQL表中能有多个NOT NULL字段
MySQL表中能有多个DEFAULT NULL字段
MySQL表中能有多个NULL DEFAULT NULL字段
MySQL表中能有多个DEFAULT字段
MySQL表中能有多个CHECK字段
MySQL表中只能有一个PRIMARY KEY字段
MySQL表中能有多个UNIQUE KEY字段
MySQL表中能有多个FOREIGN KEY字段

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
  alter_specification:
  ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or ADD INDEX [index_name] (index_col_name,...)
  or ADD PRIMARY KEY (index_col_name,...)
  or ADD UNIQUE [index_name] (index_col_name,...)
  or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or CHANGE [COLUMN] old_col_name create_definition
  or MODIFY [COLUMN] create_definition
  or DROP [COLUMN] col_name
  or DROP PRIMARY KEY
  or DROP INDEX index_name
  or RENAME [AS] new_tbl_name
  or table_options
 

转载于:https://my.oschina.net/igooglezm/blog/891970