精华内容
下载资源
问答
  • 完整性检查是围绕完整性约束条件进行的,因此完整性约束条件是完整性控制机制的核心。 完整性约束条件作用的对象可以是关系、元组、列三种。其中列约束主要是列的类型、取值范围、精度、排序等约束条件。元组的约束...
  • 数据库完整性约束

    2017-08-02 18:21:00
    完整性约束 ——数据库完整性约束简介 理解数据库完整性约束的作用;理解数据库中的各种约束的作用。 完整性约束是保证用户对数据库所做的修改不会破坏数据的一致性,是保护数据正确性和相容性的一种手段. 维护完整...

    完整性约束

              ——数据库完整性约束简介

    • 理解数据库完整性约束的作用;
    • 理解数据库中的各种约束的作用。

     

    • 完整性约束是保证用户对数据库所做的修改不会破坏数据的一致性,是保护数据正确性和相容性的一种手段.

     

    • 维护完整性
    • 在一个DBMS之中,为了能够维护数据库的完整性,必须能够提供以下的几种支持:

    提供定义完整性约束条件机制

    在数据表上定义规则,这些规则是数据库中的数据必须满足的语义约束条件;

    提供完整性检查的方法

    在更新数据库时检查更新数据是否满足完整性约束条件;

    违约处理

    DBMS发现数据违反了完整性约束条件后要采取的违约处理行为,如拒绝(NO ACTION)执行该操作,或者级联(CASCADE)执行其他操作。

    • 主要约束分类

    开发之中可以使用以下的五种约束进行定义:

    非空约束:

    如果使用了非空约束的话,则以后此字段的内容不允许设置成null;

    唯一约束:

    即:此列的内容不允许出现重复;

    主键约束:

    表示一个唯一的标识,例如:人员ID不能重复,且不能为空;

    检查约束:

    用户自行编写设置内容的检查条件;

    -外键约束(参照完整性约束):

    是在两张表上进行的关联约束,加入关联约束之后就产生父子的关系。

    隐式约束

    例如数据类型

     

    —— 非空约束:NK

    • 在正常情况下,NULL是每个属性的合法数据值。如果说现在某个字段不能为NULL,且必须存在数据,那么就可以依靠非空约束来进行控制,这样在数据更新时,此字段的内容出现NULL时就会产生错误。
    • 非空约束不允许字段为null
    • 非空约束出现错误时会提示完整的错误位置。
    • 使用非空约束:只需要在定义列的时候后面增加一个NOT NULL即可.
      • 定义member表,其中姓名不允许为空

    DROP TABLE member PURGE ;

    CREATE TABLE member(

       mid     NUMBER   ,

        name    VARCHAR2(200)   NOT NULL

    ) ;

      • member表中插入数据,name栏位不能为空

    INSERT INTO member(mid,name) VALUES(1,'wendy')

     

    —— 唯一约束:UKCONSTRAINT子句

    唯一约束,CONSTRAINTconstraint约束)

    • 唯一约束(UNIQUE,简称UK)表示的是在表中的数据不允许出现重复的情况
    • 唯一约束可以设置NULL
    • 唯一约束的列不允许重复
    • 只需要在定义列的时候后面增加一个UUNIQUE即可.
      • 定义member表,其中姓名不允许为空

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER   ,

        name    VARCHAR2(200)   NOT NULL ,

        email   VARCHAR2(50)    UNIQUE

    ) ;

    向表中添加数据:

    INSERT INTO member(mid,name,email) VALUES(1,'t','z@qq.com')

    INSERT INTO member(mid,name,email) VALUES(2,'w','z@qq.com')

    当添加第二个数据时候出现错误:ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011099)

    • 因为约束在数据库中也是一个对象,每个对象都有自己的名字,如果用户没有指定名字,那么就由系统动态分配一个,所以如上出现的错误是SYS_C0011099
    • 对于约束的名字,建议写法:“约束简写_字段名”

    为唯一约束指定一个名字

    CONSTRAINT uk_email UNIQUE(email)

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER   ,

        name    VARCHAR2(200)    NOT NULL ,

        email   VARCHAR2(50) ,

        CONSTRAINT uk_email      UNIQUE (email)

    ) ;

    唯一约束是不受空类型控制.

    如下两条记录都可以执行

    INSERT INTO member(mid,name,email) VALUES(1,'t',null)

    INSERT INTO member(mid,name,email) VALUES(2,'w',null)

     

    主键约束:PK

     

    • 主键约束:PK
      • 如果一个字段即要求唯一,又不能设置为null,则可以使用主键约束主键约束 = 非空约束 + 唯一约束),
      • 主键约束使用PRIMARY KEY(简称PK进行指定.

    在member表中的mid字段应该表示一个成员的唯一编号,

    而这个编号即不能为空,也不能重复。

    设置member表中的mid为主键

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER          PRIMARY KEY ,

        name    VARCHAR2(200)   NOT NULL ,

        email   VARCHAR2(50) ,

        CONSTRAINT uk_email UNIQUE (email)

    ) ;

    指定主键约束的名称

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER       ,

        name    VARCHAR2(200)   NOT NULL ,

        email   VARCHAR2(50) ,

        CONSTRAINT pk_mid PRIMARY KEY (mid) ,

        CONSTRAINT uk_email UNIQUE (email)

    ) ;

     

    • 复合主键
      • 在实际的开发之中,一般在一张表中只会设置一个主键,但是也允许为一张表设置多个主键,这个时候将其称为复合主键。在复合主键中,只有两个主键字段的内容完全一样,才会发生违反约束的错误

    建议使用复合主键

     

       检查约束:CK

    • 检查约束指的是对数据增加的条件过滤,表中的每行数据都必须满足指定的过滤条件。
    • 在进行数据更新操作时,如果满足检查约束所设置的条件,数据可以成功更新,如果不满足,则不能更新,
    • SQL语句中使用CHECK(简称CK)设置检查约束的条件
    • 检查约束会设置多个过滤条件,所以检查约束过多时会影响数据更新性能
    • member表中增加age字段(年龄范围是0~200岁)和sex字段(只能是男或女)

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER       ,

        name    VARCHAR2(200)   NOT NULL ,

        email   VARCHAR2(50) ,

        age     NUMBER          CHECK (age BETWEEN 0 AND 200) ,

        sex     VARCHAR2(10) ,

        CONSTRAINT pk_mid_name PRIMARY KEY (mid,name) ,

        CONSTRAINT uk_email UNIQUE (email) ,

        CONSTRAINT ck_sex   CHECK (sex IN ('',''))

    ) ;

     

    -外键约束:FK(重要)

    • 级联操作:ON DELETE CASCADEON DELETE SET NULL
    • 使用外键约束后删除表时应先删除子表再删除父表;
    • 在进行外键设置的时候,对应的字段,在父表中必须是主键或是唯一约束.
    • 如果A表和B表被设置为互为外键,那么一张表都无法删除.
      • 这个时候只能强制删除:DROP TABLE member CASCADE CONSTRAINT;
      • 不建议使用,在设计表的时候就应该考虑好先后关系
    • 外键约束的产生分析
      • 例如,现在公司要求每一位成员为公司发展提出一些更好的建议,并且希望将这些建议保存在数据表之中,那么根据这样的需求,可以设计出如图所示的设计模型。
      • 一个成员可以提出多个建议,这是一个一对多的关系.
      • 设计出了两张数据表,两张表的作用如下:
        • 人员表:用于保存成员的基本信息(编号、姓名);
        • 建议表:保存每一个成员提出的建议内容,所以在此表之中保存在了一个成员编号,即:通过此成员编号就可以和成员表进行数据的关联。

    建立member和advice表

    DROP TABLE member PURGE ;

    DROP TABLE advice PURGE ;

    CREATE TABLE member(

        mid     NUMBER       ,

        name    VARCHAR2(200)   NOT NULL ,

        CONSTRAINT pk_mid PRIMARY KEY (mid)

    ) ;

    CREATE TABLE advice(

        adid     NUMBER       ,

        content    CLOB   NOT NULL ,

            mid     NUMBER,

        CONSTRAINT pk_adid PRIMARY KEY (adid)

    ) ;

    增加一组正确的数据;

    WENDY提出了2个意见,GAGA提出了3个意见

    INSERT INTO member(mid,name) VALUES(1,'WENDY');

    INSERT INTO member(mid,name) VALUES(2,'GAGA');

    INSERT INTO advice(adid,content,mid) VALUES(1,'Miss Blair, we must go or we'll be late',1);

    INSERT INTO advice(adid,content,mid) VALUES(2,'I've been invited to a charity event',1);

    INSERT INTO advice(adid,content,mid) VALUES(3,'Okay, so whose mess is it?',2);

    INSERT INTO advice(adid,content,mid) VALUES(4,'Can i borrow you for a minutes?',2);

    INSERT INTO advice(adid,content,mid) VALUES(5,'There is nothing I can do',2);

    查询每个成员的完整信息和所提出的意见的数量

    确定所需要的数据表:

    member表

    advice表:统计建议数量

    确定关联字段:m.mid=a.mid

    SELECT m.mid,m.name,COUNT(a.mid)

    FROM member m,advice a

    WHERE m.mid=a.mid

    GROUP BY m.mid,m.name;

    增加一个建议,member表中并不存在99的信息,这时候就会出现错误数据

    INSERT INTO advice(adid,content,mid) VALUES(6,q'[nothing]',99);

    现在对于表可以分为父表(member)和子表(advice),因为子表中的数据必须参考member的数据。建议提出的成员编号应该是在member表中mid列上存在的数据.

    为了保证表的数据的有效性,只能利用外键约束完成.

    外键使用FOREIGN KEY来进行设置

    增加外键配置

    DROP TABLE member PURGE ;

    DROP TABLE advice PURGE ;

    CREATE TABLE member(

        mid     NUMBER       ,

        name    VARCHAR2(200)   NOT NULL ,

        CONSTRAINT pk_mid PRIMARY KEY (mid)

    ) ;

    CREATE TABLE advice(

        adid     NUMBER       ,

        content    CLOB   NOT NULL ,

            mid     NUMBER,

        CONSTRAINT pk_adid PRIMARY KEY (adid) ,

     CONSTRAINT fk_adid FOREIGN KEY (mid) REFERENCES member(mid)

    ) ;

    执行错误的数据插入:

    INSERT INTO advice(adid,content,mid) VALUES(6,q'[nothing]',99);

    ORA-02291: 违反完整约束条件 (SCOTT.FK_ADID) - 未找到父项关键字

     

    一旦为表中增加了外键约束,就会有新的问题:

    级联操作问题

    问题一:删除父表数据前需要先清出所有子表的对应数据

    DELECT FROM member WHERE mid=1;

    RA-02291: 违反完整约束条件 (SCOTT.FK_ADID) - 未找到父项关键字

    如果要删除这条记录,就必须要删除子表的记录,但是这样的做法并不可取.

    DELETE FROM advice WHERE mid=1;

    DELETE FROM member WHERE mid=1;

    解决方法:根据业务需求选择合适的级联操作

    【级联操作一】级联删除(ON DELETE CASCADE)

    当主数据表被删除后,对应的子表数据也应该同时被清理.这样不行,比如一个公司部门取消,不能将雇员都删除.

    【级联操作二】级联设置NULL(ON DELETE SET NULL)

    当主表数据被删除后,对应的子表数据的相应字段的内容会设置为null.

    级联删除配置(ON DELETE CASCADE)

    DROP TABLE advice PURGE ;

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER       ,

        name    VARCHAR2(200)   NOT NULL ,

        CONSTRAINT pk_mid PRIMARY KEY (mid)

    ) ;

    CREATE TABLE advice(

        adid     NUMBER       ,

        content    CLOB   NOT NULL ,

            mid     NUMBER,

        CONSTRAINT pk_adid PRIMARY KEY (adid) ,

     CONSTRAINT fk_adid FOREIGN KEY (mid) REFERENCES member(mid) ON DELETE CASCADE

    ) ;

    现在删除父表记录,父表记录以及子表记录的信息都被删除.

    DELETE FROM member WHERE mid=1;

    级联更新:级联设置NULL(ON DELETE SET NULL)

    DROP TABLE advice PURGE ;

    DROP TABLE member PURGE ;

    CREATE TABLE member(

        mid     NUMBER       ,

        name    VARCHAR2(200)   NOT NULL ,

       CONSTRAINT pk_mid PRIMARY KEY (mid)

    ) ;

    CREATE TABLE advice(

        adid     NUMBER       ,

        content    CLOB   NOT NULL ,

            mid     NUMBER,

        CONSTRAINT pk_adid PRIMARY KEY (adid) ,

     CONSTRAINT fk_adid FOREIGN KEY (mid) REFERENCES member(mid) ON DELETE SET NULL

    ) ;

    删除父表中的记录,这个时候父表中的记录会被删除,而子表中的记录会变为null空.

    DELETE FROM member WHERE mid=1;

    问题二:删除父表时需要先将子表删除

     

    查看约束

     

    • 约束依然属于数据库对象,可以直接利用数据字典查看。
    • 约束是由数据库自己创建的兑现个,所有对象都会在数据字典中进行保存
    • 可以利用user_constraints数据字典或者user_cons_columns数据字典查看.

    创建一张member表,主键不设置名字

    CREATE TABLE member(

        mid     NUMBER       PRIMARY KEY ,

        name    VARCHAR2(200)   NOT NULL ,

    ) ;

    向表中插入数据,

    INSERT INTO member(mid,name) VALUES(1,'WENDY');

    INSERT INTO member(mid,name) VALUES(1,'GAGA');

    当插入第二条数据时候会报错,ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011147)

    • 因为约束在数据库中也是一个对象,每个对象都有自己的名字,如果用户没有指定名字,那么就由系统动态分配一个,所以如上出现的错误是SYS_C0011099
    • SYS_C0011099就是数据库对象(约束)的名称

    user_constraints查看约束信息,就能看到SYS_C0011099

    Constraints_type字段可以看到约束的类型,P为主键,C为检查

    SELECT * FROM user_constraints;

    • 查看全部的约束名称、类型、约束设置对应的表名称

    SELECT constraint_name,constraint_type,table_name FROM user_constraints ;

    • 查询emp表上的全部约束

    SELECT constraint_name,constraint_type,table_name

    FROM user_constraints WHERE table_name='EMP' ;

    要查询约束所对应的字段,这时候查user_cons_columns

    SELECT * FROM user_cons_columns;

     

    修改约束(了解)

    • 约束在建立表的时候一定要同时建立;
    • 对于约束不建议对其进行修改。

     

    • 为表中增加约束

        • 语法:ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 PRIMARY KEY(约束字段) ;
        • 范例:member表的mid字段增加主键约束
          • ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid) ;
        • 范例:member表的age增加检查约束
          • ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200) ;

      启用/禁用约束

        • 禁用约束
          • ALTER TABLE 表名称 DISABLE CONSTRAINT 约束名称 [CASCADE];
          • 范例:禁用advice表中的adid主键约束“pk_adid
            • ALTER TABLE advice DISABLE CONSTRAINT pk_adid ;
        • 启用约束
          • ALTER TABLE 表名称 ENABLE CONSTRAINT 约束名称 ;
          • 范例:禁用member表中的“pk_mid”约束,此字段在advice表中是外键
            • ALTER TABLE member DISABLE CONSTRAINT pk_mid ;

      删除约束

        • ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 [CASCADE];
        • 范例:删除advice表之中的“pk_adid”约束 —— 无关联外键
          • ALTER TABLE advice DROP CONSTRAINT pk_adid ;
        • 范例:删除member表之中的“pk_mid”约束 —— 有关联外键
          • ALTER TABLE member DROP CONSTRAINT pk_mid CASCADE ;

     

     

     

    转载于:https://www.cnblogs.com/thescentedpath/p/constraint.html

    展开全文
  • 1.完整性约束条件测试 1.1完整性约束条件有哪些 PRIMARY KEY 主键 AUTO_INCREMENT 自增长 FOREIGN KEY 外键 NOT NULL 非空 UNIQUE KEY 唯一 DEFAULT 默认值 1.测试主键 设置一个主键 CREATE TABLE IF NOT EXISTS ...

    1.完整性约束条件测试

    1.1完整性约束条件有哪些

    PRIMARY KEY 主键
    AUTO_INCREMENT 自增长
    FOREIGN KEY 外键
    NOT NULL 非空
    UNIQUE KEY 唯一
    DEFAULT 默认值

    1.1.1主键

    主键:唯一标识符,身份证号和人的关系就相当于主键和记录的关系
    一般加到无意义的字段上例如:编号字段
    定义主键字段的要求:不能重复、被标志成主键的字段自动非空
    分为单字段主键和多字段主键

    --测试主键
    CREATE TABLE IF NOT EXISTS user1(
    id INT PRIMARY KEY,
    username VARCHAR(20)
    );
    Query OK, 0 rows affected (0.09 sec)
    
    DESC user1;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id       | int(11)     | NO   | PRI | NULL    |       |
    | username | varchar(20) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    2 rows in set (0.04 sec)
    --查看创建表的定义
    SHOW CREATE TABLE user1;
    +-------+----------------------------------------------------------------------------
    ---------------------------------------------------------+
    | Table | Create Table
                                                             |
    +-------+----------------------------------------------------------------------------
    ---------------------------------------------------------+
    | user1 | CREATE TABLE `user1` (
      `id` int(11) NOT NULL,
      `username` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+----------------------------------------------------------------------------
    ---------------------------------------------------------+
    1 row in set (0.00 sec)
    --主键值的唯一性
    INSERT user1 VALUES(2,'QUEEN');
    Query OK, 1 row affected (0.01 sec)
    INSERT user1 VALUES(1,'KING');
    Query OK, 1 row affected (0.00 sec)
    INSERT user1 VALUES(1,'KING');
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    --查看记录
    SELECT * FROM user1;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | KING     |
    |  2 | QUEEN    |
    +----+----------+
    2 rows in set (0.00 sec)
    --查看id为1的记录
    SELECT * FROM user1 WHERE id=1;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | KING     |
    +----+----------+
    1 row in set (0.01 sec)
    --设置两个主键
    mysql> CREATE TABLE IF NOT EXISTS userb(
        -> id INT,
        -> username VARCHAR(20),
        -> card CHAR(18),
        -> PRIMARY KEY (id,card)
        -> );
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> DESC userb;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id       | int(11)     | NO   | PRI | 0       |       |
    | username | varchar(20) | YES  |     | NULL    |       |
    | card     | char(18)    | NO   | PRI |         |       |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set (0.05 sec)
    --插入值(复合主键时,两个主键的值完全相同的记录不能插入)
    mysql> INSERT userb VALUES(1,'KING','111');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> INSERT userb VALUES(1,'QUEEN','112');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> INSERT userb VALUES(1,'PRINCESS','112');
    ERROR 1062 (23000): Duplicate entry '1-112' for key 'PRIMARY'
    mysql> SELECT * FROM userb;
    +----+----------+------+
    | id | username | card |
    +----+----------+------+
    |  1 | KING     | 111  |
    |  1 | QUEEN    | 112  |
    +----+----------+------+
    2 rows in set (0.00 sec)
    

    1.1.2自增长

    一个表中只能有一个自增长字段并且要配合主键使用,标识成自增长的字段一定是主键,但是主键不一定被标志成自增长
    自增长只对整数整数列有效果,像字符串就没有意义了
    默认从1开始每次加1

    mysql> CREATE TABLE IF NOT EXISTS userc(
    id SMALLINT KEY AUTO_INCREMENT,
    username VARCHAR(20)
    );
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> DESC userc;
    +----------+-------------+------+-----+---------+----------------+
    | Field    | Type        | Null | Key | Default | Extra          |
    +----------+-------------+------+-----+---------+----------------+
    | id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
    | username | varchar(20) | YES  |     | NULL    |                |
    +----------+-------------+------+-----+---------+----------------+
    2 rows in set (0.01 sec)
    ---自动加1
    mysql> INSERT userc VALUES(1,'KING');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> INSERT userc(username) VALUES('QUEEN');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> SELECT * FROM userc;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | KING     |
    |  2 | QUEEN    |
    +----+----------+
    2 rows in set (0.00 sec)
    
    --插入其他不连续的id值
    mysql> INSERT userc VALUES(111,'KING1');
    Query OK, 1 row affected (0.00 sec)
    mysql> SELECT * FROM userc;
    +-----+----------+
    | id  | username |
    +-----+----------+
    |   1 | KING     |
    |   2 | QUEEN    |
    | 111 | KING1    |
    +-----+----------+
    3 rows in set (0.00 sec)
    --再次插入值
    mysql> INSERT userc(username) VALUES('QUEEN1');
    Query OK, 1 row affected (0.00 sec)
    mysql> SELECT * FROM userc;
    +-----+----------+
    | id  | username |
    +-----+----------+
    |   1 | KING     |
    |   2 | QUEEN    |
    | 111 | KING1    |
    | 112 | QUEEN1   |
    +-----+----------+
    4 rows in set (0.00 sec)
    --查询细节
    mysql> SHOW CREATE TABLE userc;
    +-------+-------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------+
    | Table | Create Table
                                                                                                   |
    +-------+-------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------+
    | userc | CREATE TABLE `userc` (
      `id` smallint(6) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8 |
    +-------+-------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    --在自增长值中插入NULL 和 DEFAULT 记录
    mysql> INSERT userc VALUES(NULL,'ABC');
    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT userc VALUES(DEFAULT,'ABC');
    Query OK, 1 row affected (0.00 sec)
    mysql> SELECT * FROM userc;
    +-----+----------+
    | id  | username |
    +-----+----------+
    |   1 | KING     |
    |   2 | QUEEN    |
    | 111 | KING1    |
    | 112 | QUEEN1   |
    | 113 | ABC      |
    | 114 | ABC      |
    +-----+----------+
    6 rows in set (0.00 sec)
    
    ---修改初始自增长值
    mysql> CREATE TABLE IF NOT EXISTS userd(
    id SMALLINT KEY AUTO_INCREMENT,
    username VARCHAR(20)
    )AUTO_INCREMENT=100;
    Query OK, 0 rows affected (0.02 sec)
    mysql> SHOW CREATE TABLE userd;
    +-------+-------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------+
    | Table | Create Table
                                                                                                   |
    +-------+-------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------+
    | userd | CREATE TABLE `userd` (
      `id` smallint(6) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 |
    +-------+-------------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    mysql> INSERT userd(username) VALUES('QUEEN1');
    Query OK, 1 row affected (0.00 sec)
    mysql> SELECT * FROM userd;
    +-----+----------+
    | id  | username |
    +-----+----------+
    | 100 | QUEEN1   |
    +-----+----------+
    1 row in set (0.00 sec)
    
    --修改自增长的值
    ALTER TABLE userd AUTO_INCREMENT =500;
    
    
    

    1.1.3测试非空NOT NULL

    --测试非空(代表字段一定有值而且不能为空值)
    mysql> CREATE TABLE IF NOT EXISTS userf(
        -> id INT UNSIGNED KEY AUTO_INCREMENT,
        -> username VARCHAR(20) NOT NULL,
        -> password CHAR(32) NOT NULL,
        -> age TINYINT UNSIGNED
        -> );
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> INSERT userf(username,password) VALUES('KING','KING');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> INSERT userf(username,password,age) VALUES('KING1','KING1',12);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> SELECT *FROM userf;
    +----+----------+----------+------+
    | id | username | password | age  |
    +----+----------+----------+------+
    |  1 | KING     | KING     | NULL |
    |  2 | KING1    | KING1    |   12 |
    +----+----------+----------+------+
    2 rows in set (0.00 sec)
    --插入空值报错
    mysql> INSERT userf(username,password) VALUES(NULL,NULL);
    ERROR 1048 (23000): Column 'username' cannot be null
    

    1.1.4测试默认值DEFAULT

    头像可以设置一个默认的图片

    CREATE TABLE IF NOT EXISTS userg(
    id INT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password CHAR(32) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 18,
    addr VARCHAR(50) NOT NULL DEFAULT '北京',
    sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
    ); 
    INSERT userg(username,password) VALUES('KING','KING');
    
    
    mysql> SELECT * FROM userg;
    +----+----------+----------+------+------+-----+
    | id | username | password | age  | addr | sex |
    +----+----------+----------+------+------+-----+
    |  1 | KING     | KING     |   18 | 北京     ||
    +----+----------+----------+------+------+-----+
    
    --测试其他默认值
    
    INSERT userg VALUES(3,'QUEEN','QUEEN',29,'上海','保密');
    INSERT userg VALUES(4,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');
    

    1.1.5测试唯一UNIQUE KEY

    字段不能重复的时候使用

    CREATE TABLE IF NOT EXISTS userj(
    id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    card CHAR(18) UNIQUE
    );
    mysql> DESC userj;
    +----------+---------------------+------+-----+---------+----------------+
    | Field    | Type                | Null | Key | Default | Extra          |
    +----------+---------------------+------+-----+---------+----------------+
    | id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
    | username | varchar(20)         | NO   | UNI | NULL    |                |
    | card     | char(18)            | YES  | UNI | NULL    |                |
    +----------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.02 sec)
    
    mysql> SHOW CREATE TABLE userj;
    +-------+-------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------+
    | Table | Create Table
    
                                                                                    |
    +-------+-------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------+
    | userj | CREATE TABLE `userj` (
      `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(20) NOT NULL,
      `card` char(18) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `username` (`username`),
      UNIQUE KEY `card` (`card`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+-------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    
    mysql> INSERT userj(username) VALUES('A');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> INSERT userj(username) VALUES('A1');
    Query OK, 1 row affected (0.00 sec)
    --NULL值不算重复
    mysql> SELECT * FROM userj;
    +----+----------+------+
    | id | username | card |
    +----+----------+------+
    |  1 | A        | NULL |
    |  2 | A1       | NULL |
    +----+----------+------+
    2 rows in set (0.00 sec)
    --报错username是个唯一性字段
    mysql> INSERT userj(username) VALUES('A1');
    ERROR 1062 (23000): Duplicate entry 'A1' for key 'username'
    mysql> INSERT user9(username,card) VALUES('B','111');
    Query OK, 1 row affected (0.00 sec)
    --NULL值不算重复都成功了
    mysql> INSERT userj(username,card) VALUES('B1',NULL);
    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT userj(username,card) VALUES('B2',NULL);
    Query OK, 1 row affected (0.00 sec)
    

    1.1.6总结

    CREATE TABLE [IF NOT EXISTS] tbl_name(
    字段名称 字段类型 [UNSIGNED|ZEROFILL] [NULL|NOTNULL] [DEFAULT默认值] [[PRIMARY] KEY| UNIQUE[KEY]] [AUTO_INCREMENT一定和主键联合使用] 要按照这个顺序去写或者去省略
    )ENGINE=INNODB, CHARSET=utf8, AUTO_INCREMENT=100;
    
    展开全文
  • 数据库 完整性约束

    千次阅读 2019-05-24 14:57:42
    数据完整性分为四类: 实体完整性:规定表的每一行在表中是惟一的实体。 域完整性:是指表中的列...用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完...

    一. 数据完整性分为四类:

    1. 实体完整性:规定表的每一行在表中是惟一的实体。
    2. 域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。
    3. 参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
    4. 用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。

    二. 完整性约束的类型:

    可分为三种类型:与表有关的约束、域(Domain)约束、断言(Assertion)

    1. 与表有关的约束:是表中定义的一种约束。可在列定义时定义该约束,此时称为列约束,也可以在表定义时定义约束,此时称为表约束。
    2. 域约束:在域定义中被定义的一种约束,它与在特定域中定义的任何列都有关系。
    3. 断言:在断言定义时定义的一种约束,它可以与一个或多个表进行关联。

    1. 与表有关的约束:

    包括 列约束(表约束+NOT NULL)和
    表约束(PRIMARY KEY、foreign key、check、UNIQUE)

    (1) not null(非空)约束: 只用于定义列约束。
    语法如下: Colunm_name datatype | domain not null
    实例: create table Employee(emp_id int not null,emp_name varchar(10) not null)

    (2) unique(惟一)约束:用于指明创建惟一约束的列上的取值必须惟一。
    语法如下: Colunm_name datatype | domain unique
    实例: create table EmployeeInfo( … phone char(11) unique )
    除了在定义列时添加unique约束外,也可以将unique约束作为表约束添加。即把它作为表定义的元素。
    语法如下: [CONSTRAINT constraint_name] unique (column1,column2,…)
    实例: create table EmployeeInfo(emp_id int,constraint p_uniq unique(phone))

    (3) primary key(主键)约束:用于定义基本表的主键,起惟一标识作用,其值不能为null,也不能重复,以此来保证实体的完整性。
    语法如下: Colunm_name datatype | domain primary key
    实例: create table EmployeeInfo(emp_id int primary key)
    可以在创建表时,创建主键约束,也可创建表完成以后,创建主键,例如:
    alter table EmployeeInfo
    add constraint e_prim primary key(emp_id)

     

    primary key 与 unique的区别:

    1. 在一个表中,只能定义一个primary key约束,但可定义多个unique约束。
    2. 对于指定为primary key的一个列或多个列的组合,其中任何一个列都不能出现空值,而对于unique所约束的惟一键,则允许为null,只是null值最多有一个。

    (4) foreign key(外键)约束:定义了一个表中数据与另一个表中的数据的联系。
    foreign key约束指定某一个列或一组列作为外部键,其中包含外部键的表称为子表,包含外部键所引用的主键的表称为父表。系统保证,表在外部键上的取值要么是父表中某一主键,要么取空值,以此保证两个表之间的连接,确保了实体的参照完整性。
    语法如下:
    Colunm_name datetype | domain references table_name(column)
    [match full|partial|simple] //注:sqlserver不支持。
    [referential triggered action

    说明:table_name为父表的表名,column为父表中与外键对应的主键值。
    [match full|partial|simple]为可选子句,用于设置如何处理外键中的null值。
    [referential triggered action]可选子句,用于设置更新、删除外键列时的操作准则。
    可以为表的一列或多列创建foreign key 约束,如果为多列创建 foreign key约束,将分别与主表中的相应主键相对应。
    实例:
    create table EmployeeInfo
    (
    emp_id int primary key,
    emp_name varchar(10) not null,
    account char(4) primary key,
    phone char(11)
    address varchar(40) ,
    )
    create table Emp_Sal
    (
    emp_id int ,
    account CHAR(4) ,salary DECIMAL(5,1),
    CONSTRAINT E_SAL FOREIGN KEY(emp_id,account) REFERENCES EmployeeInfo (emp_id,account))
    )

    也可以表创建以后添加到表上。如下:
    alter table Emp_Sal
    add CONSTRAINT E_SAL FOREIGN KEY(emp_id,account) REFERENCES EmployeeInfo (emp_id,account)

    该外键的作用:确保表Emp_Sal的每个emp_id列都对应表EmployeeInfo中相应的emp_id。此时,表EmployeeInfo为父表,而表Emp_Sal为子表。子表的emp_id列参照父表的emp_id列。

    如果想在子表的emp_id 列插入一个值,首先父表的emp_id列必须存在,否则会插入失败。如果想从父表的emp_id删除一个值,则必须无删除子表emp_id列中所有与之对应的值。(注:foreign key 列上的取值可以取null)。

    潜在问题:由于foreign key列上可以取空值,DBMS将跳过对foreign key约束的检查,因此如果插入Emp_Sal如下数据:
    insert into Emp_Sal values(6,null,null) 则插入到Emp_Sal中,但其主表的相关列却不存在。

    解决办法
    (1)将联合外键的列添加not null约束,但这限制了用户的部分操作。
    (2)采用Match子句。(sqlserver不支持).

    更新、删除操作规则
    在删除或更新有primary key值的行,且该值与子表的foreign key中一个或多个值相匹配时,会引起匹配完整性的丧失。
    foreign key创建语法中,提供了可选的on updateon delete子句,也就是上面的[referential triggered action]。可用此保持引用完整性。
    on update/ on delete
    no action | cascade | restrict | set null | set default

    no action :更新或删除父表中的数据时,如果会使子表中的外键违反引用完整性,该动作将被禁止执行。不过在某些条件下,可出现暂时的,但在数据的最终状态中,不能违反外键的引用完整性。

    cascade : 当父表中被引用列的数据被更新或删除时,子表中的相应的数据也被更新或删除。

    restrict :与no action规则基本相同,只是引用列中的数据永远不能违反外键的引用完整性,暂时的也不行。

    set null :当父表数据被更新或删除时,子表中的相应数据被设置成Null值,前提是子表中的相应列允许null值。

    set default :当父表数据被更新或删除时,子表中的数据被设置成默认值。前提是子表中的相应列设置有默认值。

    (5) check(校验)约束:用来检查字段值所允许的范围。
    DBMS每当执行delete,insert或update语句时,都对这个约束过滤。如果为true,则执行。否则,取消执行并提示错误。
    列定义语法如下: Column datetype | domain check(search condition)
    表约束语法如下: constraint constraint_name check(search condition)
    实例如下:
    create table Emp_Sal(constraint validsal check(salary >=1000 and salary<=10000))
     

    2. 域约束:(sqlserver 不支持)

    语法如下:
    create domain domain_name as data type
    [default default_value]
    [constraint constraint_name] check(value condition expression)

    3. 断言约束

    不必与特定的列绑定,可以理解为能应用于多个表的check约束,因此必须在表定义之外独立创建断言。
    语法如下:
    create assertion constraint_name
    check search condition

    例如:
    create assertion name
    check (Emp_Sal.emp_id in(select emp_id from EmployeeInfo where emp_name is not null)

    添加断言后,每当试图添加或修改Emp_Sal表中的数据时,就对断言中的搜索条件求值,如果为false,则取消执行,给出提示

    展开全文
  • 一、数据库完整性概述  1.数据库的完整性: ...  DBMS应提供定义数据库完整性约束条件,并把它们存入数据库中。  ②提供完整性检查的方法  检查数据是否满足完整性约束条件的机制称为完整性
  • 提供定义完整性约束条件的机制 提供完整性检查方法 违约处理 3.2完整性分类 实体完整性:主码不能为空。 参照完整性:外码所对应的值必须为对应表的主码或空值。 用户定义完整性:某些属性值要收用户的定义而被...

    三、数据库完整性


    3.1维护完整性的三个条件

    1. 提供定义完整性约束条件的机制
    2. 提供完整性检查方法
    3. 违约处理

    3.2完整性分类

    • 实体完整性:主码不能为空。
    • 参照完整性:外码所对应的值必须为对应表的主码或空值。
    • 用户定义完整性:某些属性值要收用户的定义而被约束。

       

    3.3实体完整性

    3.3.1完整性定义

    在创建表时(create table),使用primary key定义。

    [例]:

    CREATE TABLE Student
    (Sno CHAR(9) PRIMARY KEY
     Sname CHAR(20) NOT NULL
     Ssex CHAR(2)
     Sage SMALLINT
     Sdept CHAR(20)
    )

    CREATE TABLE Student
    (Sno CHAR(9)
     Sname CHAR(20) NOT NULL
     Ssex CHAR(2)
     Sage SMALLINT
     Sdept CHAR(20)
     PRIMARY KEY (Sno)
    )

    Sno即为定义的主码。

    3.3.2完整性检查和违约处理

    实体完整性有两项自动检查,

    1.  检查主码值是否唯一,如果不唯一则拒绝插入或修改

    2. 检查主码值是否为空,为空则拒绝插入或修改

    为了方便检查主码会自动创建索引,索引的存储格式为b+数,具体实现请参照“数据结构 b树”的讨论。


    3.4参照完整性

    3.3.1参照完整性定义

    在create table中使用foreign key短语来定义哪些列为外码,用reference短语来指明这些外码参照哪些表的主码。

    [例]:

    CREATE TABLE SC
    (Sno CHAR(9) NOT NULL,
     Cno CHAR(4) NOT NULL,
     Grade SMALLINT,
     PRIMARY KEY (Sno,Cno),
     FOREIGN KEY (Sno) REFERENCE Student(Sno),
     FOREIGN KEY (Cno) REFERENCE Course(Cno)
    );

    上段代码为表SC定义了表级约束条件主码为Sno和Cno,同时Sno,Cno分别为表Student,Course的外码。

    3.3.2参照完整性检查和违约处理

    以SC表和Student表为例,破坏参照完整性的情况大致有一下四种情况。

    1. SC表中增加一个元组,元组的Sno属性在Student中找不到。

    2. 修改SC表中的元组,Sno属性在Student中找不到。

    3. 从Student表中删除一个元组,使SC表中的Sno属性在表Student中找不到对应元组。

    4. 修改Student表,使SC表中的Sno属性在表Student中找不到对应元组。

    可能破坏参照完整性的情况和违约处理
    被参照表 参照表  违约处理
    可能破坏参照完整性 插入元素 拒绝
    可能破坏参照完整性 修改外码值 拒绝
    删除元组 可能破坏参照完整性 拒绝/级连/置空
    修改主码值 可能破坏参照完整性 拒绝/级连/置空

    显示说明参照完整性违约处理示例。

     

     

     

     

     

    展开全文
  • PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录 FOREIGN KEY (FK) 标识该字段为该表的外键 NOT NULL 标识该字段不能为空 UNIQUE KEY (UK) 标识该字段的值是唯一的 AUTO_INCREMENT 标识该字段的值...
  • 为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时, DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确 保数据库中存储的数据正确、有效、相容。...
  • 完整性约束关键字 含义 DEAFAULT 设置字段值为默认值 UNIQUE KEY(UK) 约束字段的值 NOT NULL 约束字段值不为空 NOT NULL 约束字段值不为空
  • 完整性约束条件名><完整性约束条件> <完整性约束条件>包括NOT NULL,UNIQUE ,PRIMARY KEY短语以及FOREIGN KEY短语CHECK短语等 CONSTRAINT 约束 CONSTRAINT <完整性约束条件名> [PRIMARY KEY...
  • 数据库完整性约束

    2014-05-09 14:18:12
    一、数据库完整性 1、定义 关系完整性是为保证数据库中数据的正确性和相容性,对关系模型提出的某种约束条件或规则。完整性包括: 域完整性:  域完整性是保证数据库字段取值的合理性。包括限制类型(数据类型...
  • 基于《数据库系统概论》和《数据库系统概念》,阐述了数据库完整性约束如何增删改及其相关概念
  • 数据库完整性约束

    2020-12-01 21:23:15
    数据库完整性 1.定义 定义:数据库完整性是指数据的正确性和相容性 正确性:是指数据是符合现实世界语义、反映当前实际状况的. 相容性:是指数据在不同的表中是符合逻辑的 2.为维护数据库的完整性,数据库管理系统必须...
  •  约束条件与数据类型的宽度一样,都是可选参数  作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY 标示该字段为表的主键,可以唯一的标示记录 FOREIGN KEY 标示该字段为该表的外键 NOT NULL ...
  • 1、介绍什么是约束条件 2、not null(不能为空)与default(默认值) 3、unique(唯一) 4、primary key(主键,不为空且唯一) 5、auto_incerment(自增id序号) 6、foreign key(外键,表之间的指向关系,关联...
  • 文章目录完整性约束条件测试完整性约束条件有哪些1.测试主键- 设置一个主键- 设置两个主键2.测试自增长- 初始位置(默认从1开始)- 修改自增长值3.测试非空NOT NULL4.测试默认值DEFAULT5.测试唯一UNIQUE KEY 完整性...
  • 数据库完整性

    2016-07-28 21:05:00
    定义:数据的正确性和相容性 为了维护数据的完整性,DBMS必须要提供一种机制来检查数据库中的数据: 数据库完整性约束条件:加在数据库数据的语义约束条件 完整性检查:DBMS中检查数据是否满足完整性条件的机制 完整...
  • 文章目录完整性约束条件测试测试主键测试自增长初始位置开始修改自增长值测试非空NOT NULL测试默认值DEFAULT测试唯一UNIQUE KEY 完整性约束条件测试 PRIMARY KEY 主键 AUTO_INCREMENT 自增长 FOREIGN KEY 外键 NOT ...
  • 1. 何为完整性约束条件 1.1 列级约束条件 1.2 表级约束条件 1.3 语法格式 2. 主键(PRIMARY KEY)约束 3. 唯一性(UNIQUE)约束 4. 空与非空(NULL/NOT NULL)约束 5. 默认值(DEFAULT)约束 6. 检查(CHECK)...
  • 总结一下,数据库完整性约束几个列子 正文 一、静态完整性:Create Table CreateTable有三种功能:定义关系模式、定义完整性约束和定义物理存储特性 定义完整性约束条件: 列完整性 表完整性 Col_constr列约束 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,401
精华内容 560
关键字:

数据库完整性约束条件