SQL——数据完整性

一．数据完整性

     就是为了防止数据库中存在不符合语义规定的数据，和防止因输入输出错误而造成的无效操作或者错误信息而提出的，制定的一些对元组、属性和域的一些约束和规定。

     解决办法由：规则，默认值，约束和触发器等。

二．约束

    用于增强数据完整性，防止合法用户使用数据库时，想数据库中添加不符合语义的数据。约束分为三种：与表有关的约束，域约束，断言约束。

   。与表有关的约束：行约束，列约束。

   。域约束：在域定义中被定义的，并且与在域中定义的任何列都有关系的一种约束。

   。.断言约束：在断言定义时定义的一种约束，可以与一个或者多个表有关。

下面是DEFAULT约束，CHECK约束，PRIMARYKEY约束，UNIQUE约束，FOREIGN KEY约束，NOU NULL约束，列约束，表约束。

1．DEFAULT约束

     主要就是针对表中的某个列，创建一个默认值，在用户插入值时，如果没有给定值，那就默认为此值。

    (1).创建数字类型的DEFAULT约束

     数字类型有：整数（bigint , int , smallint , tinyint），精确数字（decimal[p( , s)] , 

numeric[p( , s]），浮点数（float[(n)] , real），货币值（money , smallmoney）。

假设表goods，属性：gNO,gNAME,gCOUNT,gPRICE,gADDRESS,StartDate

现在为gCOUNT和gPRICE创建默认值：

ALTER TABLE goods ADD DEFAULT 0 FOR gCOUNT;

ALTER TABLE goods ADD DEFAULT 0 FOR gPRICE;

输入以下语句：INSERT INTO goods (gNO,gNAME,gADDRESS) VALUES('1001','毛巾','吉林长春','2010-1-20 15:25:30.120')。执行成功后，在擦看一下，你会发现结果中gCOUNT和gPRICE列的值为0。

注意：在执行插入语句时，一定要列出插入的列，要不然你会遇到插入数据列与表属性列不一致的错误。

    (2).设置DEFAULT日期型约束

     为goods表的货物到货日期StartDate设置默认值为系统当前日期。

     ALERT TABLE goods ADD DEFAULT GetDate() FOR StartDate;

     插入数据：INSERT INTO goods(gNO,gNAME,gADDRESS) VALUES ('1005','牙刷','上海浦东新区')。执行代码成功后，再次查看数据就能看到到货日期是当前你的计算机的时间。

(3).设置DEFAULT字符型约束

 为一个学生信息表student的sex属性设置默认值为“男”，先假设这个表已经存在。

 ALERT TABLE student ADD DEFAULT  '男'  FOR sex;

 INSERT INTO student (sno,sname,sage) VALUES('1001','理想',19);

 执行成功后，查看表中的信息，sex被设置为'男'。

2. CHECK约束

   CHECK约束是用的最广泛的约束，用来检查字段值所允许的范围，单一数据列可以有多个CHECK约束，一个CHECK约束可以保护多个数据列，可以被定义为表约束，列约束和域约束等。语法结构：CONSTRAINT constraint_name CHECK(condition)

Condition为条件，可以是一个表达式，也可以是一个子查询语句。

    (1).创建单个CHECK 约束

    CREATE TABLE student

    (

    Sno   int,

    Sname char(5),

    Sex   char(2),

    Sage  int,

    Saddress char(20)

    CONSTRAINT conAddress CHECK(Saddress <> '无')

    )

    创建成功后，向这个表里加入数据，若Saddress属性列为NULL那么你将得到一个“INSERT语句与CHECK约束冲突”的错误消息。

   (2).创建多条件的CHECK约束。

    CREATE TABLE student

    (

    Sno   int,

    Sname char(5),

    Sex   char(2),

    Sage  int,

    Saddress char(20)

    CONSTRAINT conSage CHECK(Sex IN('男' , '女') AND (Sage>0 AND Sage<60))

    )

    创建成功后，向表中插入数据，若性别与年龄不是约束规定的值，那么你也会受到如上面的错误信息。

3. PRIMARY KEY约束

   这主要是用来为表创建主键，主键就是一个属性列，被设为主键的列不能为空。

CREATE TABLE student

   (

   Sno   int  PRIMARY KEY,

   Sname char(5),

   Sex   char(2),

   Sage  int,

   Saddress char(20)

   )

   注意：

  (1).PRIMARY KEY约束的列不能为空。

  (2).PRIMARY KEY约束的列不允许添加重复的信息。

  (3).若要修改PRIMARY KEY约束，必须先删除现有的PRIMARY KEY约束，然后再创建新的PRIMARY KEY约束。

  (4).若另一个表的FOREIGN KEY引用了PRIMARY KEY约束，则必须先删除FOREIGN KEY约束，才能删除PRIMARY KEY约束。

4. UNIQUE约束

   唯一性约束，用于指明某一列或多列的取值不需唯一，不允许重复。在表里面创建此约束。

  (1).创建UNIQUE约束

    CREATE TABLE student

    (

    Sno   int  PRIMARY KEY,

    Sname char(5),

    Sex   char(2),

    Sage  int,

    Sphone char(12)  UNIQUE,

    Saddress char(20)

    )

   上面创建的表中，我们约束了每个学生电话号码Sphone必须唯一，若插入学生信息时某两个学生的电话号码重复了，你就会收到“违反了UNIQUE KEY约束不能在对象中插入重复键”的错误信息。

   (2).在已经存在的数据表中创建唯一约束

    修改语法：

    ALERT TABLE table_name

    ADD [CONSTRAINT constraint_name] datetype UNIQUE{column[, ...n]}

    对上面创建的学生表进行修改，

    ALERT TABLE student

    ADD stu_unique char(5) UNIQUE(Sname)

   创建成功后就不能在姓名这一列插入相同的学生名字了。

5. FOREIGN KEY约束

   此约束是指某一列或者多列作为外键，包含外键的表叫子表，包含外键所引用的主键的表叫父表。用于实现两个表之间数据联系的一个或多个列的组合。外键约束值可以去NULL，但所引用的键为主键除外。

   创建表时，可以创建外键约束作为表定义的一部分。

   修改外键约束时，必须将现有的外键约束删除，在重建。

   可以在表中一列或多列创建外键约束，若多列创建外键约束，则将分别在父表中的相应的主键相对应。

   CREATE TABLE student

   (

   Sno   int  PRIMARY KEY,

   Sname char(5),

   Sex   char(2),

   Sage  int,

   Sphone char(12)  UNIQUE,

   Saddress char(20)

   )

  创建一个课程表：

   CREATE TABLE SC

   (

   Sno  INT ,

   Cno  INT,--课程号

   Grade INT,

   FOREIGN KEY(Sno) REFERENCES student(Sno)

   )

   此课程表是按照学生选课信息建立的，所以在插入信息时，一定要插入Sno存在于student表中的学生选课信息。

6. NOT NULL约束

   很显然，此约束是用来检查列的每条记录是否非空。在SQL中， PRIMARY KEY约束将自动约束为NOT NULL。如下：

   CREATE TABLE Teache

   (

   Tid  INT  NOT NULL,

   Tname char(10),

   Tage  INT,

   Tphone  char(11) NOT NULL

   )

   使用NOT NULL关键字要求列必须在插入或更新操作期间具有一个输入值。若存在默认值，则不用输入也可。

7.  列约束和表约束

   与表有关的约束包括列约束和表约束。

   表约束：UNIQUE约束，PRIMARY KEY约束，FOREIGN KEY约束和CHECK约束。

   列约束：除了包括表约束的所有约束外，外加NOT NULL约束。它是对某一列的约束，直接跟在列定义的后面。

   表约束的基本语法：

   [CONSTRAINT  constraint_name]

   constraint_type (column 1,column 2,column 3,...)

   constraint_type表示约束类型，可以是UNIQUE，PRIMARY KEY，FOREIGN KEY，CHECK等约束。