使用Access应用程序定义表SQL

来源:www.accessoft.com 点击数:2211 　 | 收藏 | 复制

时　间:2022-09-03 10:00:09
作　者:杨雪 ID：42182 城市：南京
摘　要:使用 Access 应用程序定义表SQL。
正　文:

关系是两个或多个表之间建立的关联。关系基于多个表中的公共字段，通常包括主键和外键。

主键是用于唯一标识表中每个记录的一个字段（或多个字段）。主键有三个要求：不能为空、必须唯一、每个表只能定义一个主键。可以通过创建表后创建主键索引，或在表声明中使用 CONSTRAINT 子句来定义主键，如本节稍后部分的示例中所示。约束限制字段中输入的值。

外键是一个表中引用另一个表中主键的字段。来自两个表的字段的数据完全相同，在包含外键记录的表（外表）有匹配或相关记录之前，包含主键记录的表（主表）必须已有记录。与主键一样，可以使用 CONSTRAINT 子句在表声明中定义外键。

实际上有三种关系：

一对一：对于主表中的每个记录，在外表中有且只有一个记录。

一对多：对于主表中的每个记录，在外表中有一个或多个相关记录。

多对多：对于主表中的每个记录，在外表中有多个相关记录，对于外表中的每个记录，在主表中有多个相关记录。

例如，假设要向发票数据库中添加一个发票表。客户表中的每个客户在发票表中都可以有多个发票，这是典型的一对多方案。可以从客户表中获得主键，在发票表中将主键定义为外键，从而在表之间建立正确的关系。

定义表之间的关系时，必须在字段级进行 CONSTRAINT 声明。这意味着约束是在 Create TABLE 语句中定义的。要应用约束，可在字段声明后使用 CONSTRAINT 关键字，命名约束，命名约束引用的表，并命名该表内将组成匹配外键的字段。

以下语句假设已经建立了 tblCustomers 表，并且假设对“CustomerID”字段定义了一个主键。现在，该语句通过对“InvoiceID”字段定义主键来建立 tblInvoices 表。该语句还通过在 tblInvoices 表中定义另一个“CustomerID”字段在 tblCustomers 和 tblInvoices 表之间建立一对多关系。此字段被定义为引用客户表中“CustomerID”字段的外键。请注意，每个约束的名称后面都有 CONSTRAINT 关键字。

Create TABLE tblInvoices  
    (InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY, 
    CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID  
        REFERENCES tblCustomers (CustomerID), 
    InvoiceDate DATETIME, 
    Amount CURRENCY)

请注意，发票表的主键索引 (PK_InvoiceID) 在 Create TABLE 语句中声明。为了增强主键的性能，系统会自动为主键创建索引，因此无需使用单独的 Create INDEX 语句。现在，创建一个包含每个客户的发货地址的发货表。假设每个客户记录只有一个发货记录，因此你将建立一对一关系。

Create TABLE tblShipping  
    (CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY 
        REFERENCES tblCustomers (CustomerID), 
    Address TEXT(50), 
    City TEXT(50), 
    State TEXT(2), 
    Zip TEXT(10))

请注意，“CustomerID”字段既是发货表的主键，又是引用客户表的外键。

约束

约束可以用于建立主键和参照完整性，并且可以用于限制可插入到字段中的值。一般来说，约束可以用于保持数据库中数据的完整性和一致性。

有两种约束：单字段或字段级约束以及多字段或表级约束。这两种约束都可以用于 Create TABLE 或 Alter TABLE 语句。

声明字段和数据类型之后，使用字段本身对单字段约束（也称为列级约束）进行声明。使用客户表并对“CustomerID”字段创建单字段主键。要添加约束，请将 CONSTRAINT 关键字与字段名称一起使用。

Alter TABLE tblCustomers 
   Alter COLUMN CustomerID INTEGER 
   CONSTRAINT PK_tblCustomers PRIMARY KEY

请注意，约束的名称是给定的。可以使用快捷方式声明完全省略 CONSTRAINT 子句的主键。

Alter TABLE tblCustomers 
   Alter COLUMN CustomerID INTEGER PRIMARY KEY

但是，使用快捷方式会导致 Access 随机生成约束名称，这样会使在代码中引用很困难。建议始终命名约束。

要删除约束，请将 Drop CONSTRAINT 子句与 Alter TABLE 语句一起使用，并提供该约束的名称。

Alter TABLE tblCustomers 
   Drop CONSTRAINT PK_tblCustomers

约束还可以用于限制字段的允许值。可以将值限制为 NOT NULL 或 UNIQUE ，也可以定义检查约束，这是一种可应用于字段的业务规则。假设您要将名字字段和姓氏字段的值限制为唯一，这意味着表中任何两个记录的名字和姓氏组合绝不应该相同。因为这是一个多字段约束，所以它在表级声明，而不是在字段级声明。使用 ADD CONSTRAINT 子句并定义一个多字段列表。

Alter TABLE tblCustomers 
   ADD CONSTRAINT CustomerID UNIQUE 
   ([Last Name], [First Name])

检查约束是一种强大的 SQL 功能，它允许您通过以下方式向表中添加数据验证：创建可以引用单字段的表达式，或者在一个或多个表中引用多个字段的表达式。假设您要确保在发票记录中输入的金额始终大于￥0.00。为此，请通过在 Alter TABLE 语句的 ADD CONSTRAINT 子句中声明 CHECK 关键字和验证表达式来使用检查约束。

Alter TABLE tblInvoices 
   ADD CONSTRAINT CheckAmount 
   CHECK (Amount > 0)

用于定义检查约束的表达式还可以引用同一个表中的多个字段或其他表中的字段，并且可以使用 Access SQL 中有效的任何操作，如 Select 语句、数学运算符和聚合函数。定义检查约束的表达式长度不能超过 64 个字符。

假设在将客户添加到客户表之前要检查每个客户的信贷限额。使用带有 ADD COLUMN 和 CONSTRAINT 子句的 Alter TABLE 语句创建一个约束，该约束将查阅 CreditLimit 表中的值以验证客户的信贷限额。使用以下 SQL 语句可以创建 tblCreditLimit 表、向 tblCustomers 表中添加"CustomerLimit"字段、向 tblCustomers 表中添加检查约束，以及测试该检查约束。