MYSQL数据的约束

 

约束类型

­

　　1。NOT NULL 约束

　　

　　　　该约束用于指定列不能为NULL,当插入数据时，必须要为NOT NULL列提供数据。

­

　　2。唯一约束

­

　　　　唯一约束用于指定列的数据不能重复。

　　　　注意：唯一约束列可以为NULL

                         当指定唯一约束时，系统会自动基于唯一约束列建立索引

­

　　3。主键约束

­

　　　主键约束用于唯一标识表行的数据。当指定了主键约束之后，主键约束列值不能重复，并且主键约束列值不能为NULL.

           注意：当指定主键约束时，系统会自动基于主键列建立索引

　　　　　　一张表只能有一个主键约束

­

　　4。外部键约束

­

　　　外部键约束用于定义两张表之间的关系，外部键列数据必须在主键列中存在，或者为NULL.

­

    5.检查约束

­

　　　检查约束用于强制列值必须满足的条件。

­

­

     约束状态

­

­

　　1。DISABLE NOVALIDATE

­

             当约束处于DISABLE NOVALIDATE状态时，虽然在数据字典中保留了约束信息，但约束列的数据变化可以不遵守约束规则。

­

　　2。DISABLE VALIDATE

­

         当约束处于DISABLE VALIDATE 状态时，即不允许修改约束列的定义，也不允许在表上执行INSERT/UPDATE/DELETE操作。

­

　　 3。ENABLE NOVALIDATE

­

         当约束处于ENABLE NOVALIDATE状态时，不会对已存在数据进行检查，而只会检查新的数据。

­

　　4。ENABLE VALIDATE

­

       ENABLE VALIDATE是约束的默认状态

­

­

­

　　定义约束

­

­

　　1。在建表时定义约束分两种定义：

　　　　　　　　　　　　　　　　　　列级定义约束

　　　　　　　　　　　　　　　　　　表级定义约束

­

　　　　注意：NOT NULL 约束只能在列级定义

­

　　　　格式：             列级：column[CONSTRAINT constraint_name] constraint_type

­

                         表级：column[CONSTRAINT constraint_name] constraint_type(column,...)

­

­

             例子：

　　　　　　　              create table department(

                          dept_id  number(2)

                          constraint pk_department primary key,

                           name   varchar2(20));

­

­

                           create table employee(

                           emp_id number(4) constraint pk_employee primary key

                           using index tablespace index,

                           name varchar2(20), job varchar2(10),

                           manager_id number(4),

                           salary number(7,2),

                           dept_id number(2),

                           constraint fk_department foreign key (dept_id)

                           references department(dept_id));

­

              注释：USING INDEX用于指定索引段所在的表空间

­

­

­

­

­

　　　　在建表后增加约束

­

­

　　　　注意：NOT NULL 是用MODIFY选项来完成的，而增加其它约束是用ADD选项来完成的

­

　　　   ALTER TABLE nanjing ADD[CONSTRAINT constraint]type (column)

­

­

               例如：    为department表的name列指定NOT NULL约束

­

　　　　alter table department modify name not null;

­

                          为department表name 列上增加唯一约束

­

　　　　alter table department add constraint u_deptname unique(name);

­

             　　　　     为department表指定检查约束

­

　　　　alter table department add constraint check_job check

           (job in('manager','salesman','clerk');

­

­

­

­

        禁止和激活约束

­

­

　　　当用EXPORT工具装载数据时，为了加快数据装载速度，应该首先在装载数据库之前禁止约束，然后进行数据装载，最后在数据装载完毕之后激活约束。

­

　　　1。禁止约束

­

　　　alter table employee disable novalidate constraint check_job

­

          注意：当禁止主键约束或唯一约束之后，会自动删除主键约束和唯一约束列上的唯一索引

­

　　　2。使用NOVALIDATE选项激活约束

­

　　　alter table employee enable novalidate constraint check_job

­

          注意：用NOVALIDATE选项激活约束事不会对已存在的数据进行检查，而只会确保新增加的数据必须要满足约束规则。

　　　

　　　3。使用VALIDATE选项激活约束

　　

　　　alter table employee enable validate constraint check_job

­

  　　注意：使用VALIDATE激活约束时，如果存在数据不满足约束规则，则会导致失败

­

　　　解救办法：

­

　　　      (1).SQL>@/opt/OracleHome/Ora9i/rdbms/admin/utlexcpt.sql

­

            (2).ALTER TABLE employee ENABLE VALIDATE  CONSTRAINT  check_job EXCEPTIONS  INTO  exceptions;

­

            (3).SELECT  job,rowid   FROM  employee  WHERE  rowid  IN  (SELECT  row_id  FROM   exceptions) FOR UPDATE;

­

­

            (4).UPDATE employee  SET   job='SALESMAN'  WHERE  rowid  IN (SELECT  row_id  FROM exception);

­

             (5).ALTER TABLE employee ENABLE VALIDATE  CONSTRAINT check_job;

­

­

           删除约束

­

　　　ALTER TABLE employee DROP CONSTRAINT    check_job;

­

           注意：如果在两张表之间存在主外键关系，那么当删除主表的主键约束时必须带有cascade选项

­

　　　ALTER TABLE department DROP PRIMARY KEY CASCADE;

­

­

           显示约束信息

­

　　　显示表的约束信息

­

　　　select constraint_name,constraint_type,status from user_constraints

           where table_name='EMPLOYEE';

­

     显示约束列

­

　　　select cloumn_name,position from user_cons_columns

           where constraint_name='CHECK-JOB';