[SQL]基本表的定义及其完整性约束
阅读原文时间:2023年07月08日阅读:2

在使用数据库时,绝大多数时间都是在使用基本表。

截图来源: https://www.w3school.com.cn/sql/sql_datatypes.asp

基本格式如下:

create table <表名>
(
    <列名1> <数据类型> [<列级完整性约束条件>],
    ……
    <列名n> <数据类型> [<列级完整性约束条件>]
    [, <表级完整性约束条件>]
)

如果完整性约束涉及到多个属性列,则必须定义在表级上,否则可以定义在列级或表级

如下创建一个Persons基本表:

use test -- 在该数据库中创建基本表
go
create table Persons
(
    Id_P int primary key, -- Id_P为主键
    LastName nvarchar(20),
    FirstName nvarchar(20),
    Address nvarchar(50),
    City nvarchar(10)
)
go

完整性约束的用途是限制输入到基本表中的值的范围,SQL的完整性约束可分为列级完整性约束和表级完整性约束:

  • 列级完整性约束:针对关系属性值的限定条件,只能应用在一列上
  • 表级完整性约束:涉及关系中的多个属性的限制条件,可以应用在一个基本表中的多个列上。

如果完整性约束涉及到多个属性列,则必须定义在表级上,否则可以定义在列级或表级。当创建完整性约束之后,它作为基本表定义的一部分,存入数据字典中。

primary key约束

主键(primary key)约束是实体完整性约束,primary key用于定义主键,它保证主键的唯一性与非空性。一个基本表的主键由一列或者几列构成,可以定义在列级或者表级上,但是不能在两个级别上进行定义。

定义主键约束的方法如下:

  1. primary key之间写在列名及其数据类型之后。例如:

    create table test
    (
        col_name int primary key,
        ……
    )
  2. 按照语法在相应的列名及其数据类型后单独列出:

    constraint <约束名> primary key

    约束名就是主键的名字。例如:

    create table test
    (
        col_name int constraint PK_name primary key,
        ……
    )
  3. 在create table语句中定义完所有列之后定义表级主键约束:

    primary key (列名1, 列名2, ……)

    constraint PK_name primary key (列名1, 列名2, ……)

    例如:

    create table test
    (
        col_name1 int,
        col_name2 nvarchar(10),
        ……
        constraint PK_name primary key (col_name1, col_name2)
    )

foreign key约束

外键(foreign key)约束也称为参照表约束,用于定义参照完整性,即用来维护两个基本表之间的一致性关系。外键的建立主要是通过将一个基本表的主键(或者unique唯一性约束)所在的列包含在另一个表中,这些列就是另一个表的外键。

例如,因为销售订单和销售人员之间存在一种逻辑关系,所以 Sales.SalesOrderHeader 表含有一个指向 Sales.SalesPerson 表的外键链接。 SalesOrderHeader 表中的 SalesPersonID 列与 SalesPerson 表中的主键列相对应。 SalesOrderHeader 表中的 SalesPersonID 列是指向 SalesPerson 表的外键。 通过创建此外键关系,如果 SalesPerson 表的主键中不存在 SalesPersonID 的值,则 SalesPersonID 的值将无法插入到 SalesOrderHeader 表。

外键的作用不只是对输入自身表格的数据进行限制,同时也限制了对主键所在基本表的数据进行修改。主键所在的数据被另一张表的外键所引用时,用户将无法对主键中的数据进行修改或者删除,除非事先删除或修改引用的数据。

foreign key约束定义外键的方法如下:

  1. 如果外键只有一列,可以在它的列名和类型后面直接用references说明它参照了某个基本表的某些列(必须为主键):

    references <表名> (<列名>)
  2. 在属性列表后面增加一个或几个外键说明,其语法格式为:

    foreign key (<列名>) references <表名> (<列名>)

    其中,第一个列名是外键,第二个列名是被参照基本表中的列名。

  3. 在相应的列名及其类型后单独列出并指定约束名,语法格式为:

    constraint <约束名> foreign key (<列名>) references <表名> (<列名>)

unique约束

unique约束是唯一性约束,主要用来确保不受primary key约束的列上的数据的唯一性,两者的区别主要表现如下:

  1. unique约束主要用在非主键的一列或者多列上要求数据唯一的情况。
  2. unique约束允许该列上存在NULL值,而主键不允许。
  3. 一个表可设置多个unique约束,但是只能有一个主键约束。

unique约束可以在列级和表级上设置,但是要在多个列上设置unique约束必须设置表级约束。

定义unique约束的方法:

  1. unique约束直接写在列名和数据类型之后

  2. 按照语法在相应的列名及其类型后单独列出:

    constraint <约束名> unique
  3. 表级unique约束:

    constraint <约束名> unique (<列名1> [, <列名2>, <列名2>,……])

check约束

check约束时检查约束,它通过检查输入表列的数据的值来维护值域的完整性,它依次检查每一个要进入数据库的数据,只有符合条件的数据才允许通过。check约束通过对一个逻辑表达式的结果进行判断来对数据进行检查。

default约束

default约束用于向列中插入默认值,如果没有规定其他的值,则添加默认值到所有新记录中。

not null或null约束

not null约束不允许列值为空,null约束允许列值为空。

create table 图书
(
    图书号 varchar(15),
    图书名称 varchar(30),
    主编 varchar(30) not null,
    出版社 varchar(15) default '清华大学出版社'
    单价 float,
    constraint CH_dj check (单价>=0 and 单价<=1000)
)