SQL FOREIGN KEY 约束- 保障表之间关系完整性的关键规则

小万哥
• 阅读 288

SQL FOREIGN KEY 约束

SQL FOREIGN KEY 约束用于防止破坏表之间关系的操作。FOREIGN KEY 是一张表中的字段(或字段集合),它引用另一张表中的主键。具有外键的表称为子表,具有主键的表称为被引用表或父表。

以下是两个表的例子:

Persons 表

PersonID  LastName  FirstName  Age
1          Hansen      Ola          30
2          Svendson  Tove      23
3          Pettersen  Kari      20

Orders 表

OrderID  OrderNumber  PersonID
1      77895      3
2      44678      3
3      22456      2
4      24562      1

注意,"Orders" 表中的 "PersonID" 列指向 "Persons" 表中的 "PersonID" 列。"Persons" 表中的 "PersonID" 列是 "Persons" 表中的主键。"Orders" 表中的 "PersonID" 列是 "Orders" 表中的外键。

FOREIGN KEY 约束防止将无效数据插入到外键列中,因为它必须是父表中包含的值之一。

在 CREATE TABLE 时使用 SQL FOREIGN KEY

以下 SQL 在创建 "Orders" 表时在 "PersonID" 列上创建了一个 FOREIGN KEY

对于 MySQL:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

对于 SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

要允许对 FOREIGN KEY 约束进行命名,并在多列上定义 FOREIGN KEY 约束,请使用以下 SQL 语法:

对于 MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

在 ALTER TABLE 时使用 SQL FOREIGN KEY

要在表已经创建的情况下在 "PersonID" 列上创建 FOREIGN KEY 约束,请使用以下 SQL:

对于 MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

要允许对 FOREIGN KEY 约束进行命名,并在多列上定义 FOREIGN KEY 约束,请使用以下 SQL 语法:

对于 MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

删除 FOREIGN KEY 约束

要删除 FOREIGN KEY 约束,请使用以下 SQL:

对于 MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

对于 SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

通过这些 SQL 语句,您可以在数据库中定义和管理 FOREIGN KEY 约束,以确保表之间的关系得到维护。

SQL CHECK 约束

SQL CHECK 约束用于限制可以放入列中的值范围。如果在列上定义了 CHECK 约束,它将仅允许为该列指定某些值。如果在表上定义了 CHECK 约束,它可以基于行中其他列的值来限制某些列中的值。

在 CREATE TABLE 时使用 SQL CHECK

以下 SQL 在创建 "Persons" 表时在 "Age" 列上创建了一个 CHECK 约束。CHECK 约束确保一个人的年龄必须是 18 岁或以上:

对于 MySQL:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

对于 SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

要允许对 CHECK 约束进行命名,并在多列上定义 CHECK 约束,请使用以下 SQL 语法:

对于 MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

在 ALTER TABLE 时使用 SQL CHECK

要在表已经创建的情况下在 "Age" 列上创建 CHECK 约束,请使用以下 SQL:

对于 MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (Age>=18);

要允许对 CHECK 约束进行命名,并在多列上定义 CHECK 约束,请使用以下 SQL 语法:

对于 MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

删除 CHECK 约束

要删除 CHECK 约束,请使用以下 SQL:

对于 SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

对于 MySQL:

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

通过这些 SQL 语句,您可以在数据库中定义和管理 CHECK 约束,以确保列中的值满足指定的条件。

SQL DEFAULT 约束

SQL DEFAULT 约束用于为列设置默认值。如果没有指定其他值,将在所有新记录中添加默认值。

在 CREATE TABLE 时使用 SQL DEFAULT

以下 SQL 在创建 "Persons" 表时为 "City" 列设置了 DEFAULT 值:

对于 MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

DEFAULT 约束还可以用于通过使用诸如 GETDATE() 之类的函数插入系统值:

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

在 ALTER TABLE 时使用 SQL DEFAULT

要在表已经创建的情况下在 "City" 列上创建 DEFAULT 约束,请使用以下 SQL:

对于 MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

对于 SQL Server:

ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;

对于 MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';

对于 Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';

删除 DEFAULT 约束

要删除 DEFAULT 约束,请使用以下 SQL:

对于 MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;

对于 SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

对于 SQL Server:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

通过这些 SQL 语句,您可以在数据库中为列设置默认值,确保在插入新记录时,如果未提供值,将使用指定的默认值。

最后

为了方便其他设备和平台的小伙伴观看往期文章:

微信公众号搜索:Let us Coding,关注后即可获取最新文章推送

看完如果觉得有帮助,欢迎 点赞、收藏、关注

点赞
收藏
评论区
推荐文章
Easter79 Easter79
2年前
sqlserver2005创建唯一约束的方法
对于一个表中非主键列的指定列,唯一(UNIQUE约束|:强制非主键上的实体完整性的约束。UNIQUE约束确保未输入重复值,并创建一个索引以增强性能。)约束确保不会输入重复的值。例如,在employee表中emp\_id列是主键,可以定义一个唯一约束来要求表中社会安全号码(ssn)列的项是唯一的。在数据库关系图中,可以使用"索引/键"属性页创建、
Wesley13 Wesley13
2年前
MySQL字段约束及多表查询
前言:mysql的字段约束是以后必不可免的,下面主要写了四个:主键约束用于唯一且不能为空;非空约束即不能为空可以重复;唯一约束即可以为空但必须唯一;外键约束是让表与表之间有一定的关联;当然如何使用还看下文,多表就不在这总结了。如果你对前面的知识有所遗忘或感兴趣MySQL数据库表的模糊/多行/分组/排序/分页查询以及字mysql数据类型的讲解
Wesley13 Wesley13
2年前
MySQL创建表时加入的约束以及外键约束的的意义
1,创建表时加入的约束a) 非空约束,notnullb) 唯一约束,uniquec) 主键约束,primarykeyd) 外键约束,foreignkey1,非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空droptableifexistst_studen
Wesley13 Wesley13
2年前
MySQL字段完整性约束(重要)
\TOC\完整性约束(重要)primarykey:主键,唯一标识,表都会拥有,不设置为默认找第一个不空,唯一字段,未标识则创建隐藏字段foreignkey:外键,外键要通过foreignkey语法建立表与表之间的关联uniquekey:唯一性数据,该条字段的值需要保证唯一
Wesley13 Wesley13
2年前
mysql 外键(foreign key)的详解和实例
外键具有保持数据完整性和一致性的机制,对业务处理有着很好的校验作用。白话简介user表:id为主键profile表:uid为主键简单来说,若表profile的uid列作为表外键(外建名称:user_profile),以表user 做为主表,以其id列做为参照(referenc
Easter79 Easter79
2年前
SQL JOIN语法,以及JOIN where 和and区别,还有where和join效率问题。
语法join用于根据两个或多个表中的列之间的关系,从这些表中查询数据。Join和Key有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。我们就需要执行join。数据库中的表可通过键将彼此联系起来。主键(PrimaryKey)是一个列,在这个列中的每一行的值都是唯一的。在表中,每个主键的值都是唯一的。这
Wesley13 Wesley13
2年前
MySQL(三) 完整性约束
一.介绍  约束条件与数据类型的宽度意义,都是可选参数.  作用:用于保证数据的完整性和一致性.  主要分为:PRIMARYKEY(PK)标识该字段为该表的主键,可以唯一的标识记录FOREIGNKEY(FK)标识该字段为该表的外键NOTNULL标识该字段不能为空
Stella981 Stella981
2年前
SQL Server关系的创建
如果两个表的相关列都是主键或具有唯一约束,创建的就是一对一关系。如果只有一列具有主键或唯一约束,则创建的时一对多关系关联字段的字符类型必须相同。1\.一对一关系USEHowiecreatetablepurchases(docentryintconstraintpk_prpr
Easter79 Easter79
2年前
SQL Server关系的创建
如果两个表的相关列都是主键或具有唯一约束,创建的就是一对一关系。如果只有一列具有主键或唯一约束,则创建的时一对多关系关联字段的字符类型必须相同。1\.一对一关系USEHowiecreatetablepurchases(docentryintconstraintpk_prpr
Stella981 Stella981
2年前
SQL JOIN语法,以及JOIN where 和and区别,还有where和join效率问题。
语法join用于根据两个或多个表中的列之间的关系,从这些表中查询数据。Join和Key有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。我们就需要执行join。数据库中的表可通过键将彼此联系起来。主键(PrimaryKey)是一个列,在这个列中的每一行的值都是唯一的。在表中,每个主键的值都是唯一的。这