全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

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

作者：小万哥
  • 2023-12-08
    广东

  • 本文字数：2772 字

    阅读完需：约 9 分钟

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

SQL FOREIGN KEY 约束

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


以下是两个表的例子：


Persons 表


PersonID  LastName  FirstName  Age1          Hansen      Ola          302          Svendson  Tove      233          Pettersen  Kari      20
复制代码


Orders 表


OrderID  OrderNumber  PersonID1      77895      32      44678      33      22456      24      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 OrdersADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
复制代码


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

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

ALTER TABLE OrdersADD CONSTRAINT FK_PersonOrderFOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
复制代码

删除 FOREIGN KEY 约束

要删除 FOREIGN KEY 约束，请使用以下 SQL：

对于 MySQL：

ALTER TABLE OrdersDROP FOREIGN KEY FK_PersonOrder;
复制代码

对于 SQL Server / Oracle / MS Access：

ALTER TABLE OrdersDROP 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 PersonsADD CHECK (Age>=18);
复制代码


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

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

ALTER TABLE PersonsADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
复制代码

删除 CHECK 约束

要删除 CHECK 约束，请使用以下 SQL：

对于 SQL Server / Oracle / MS Access：

ALTER TABLE PersonsDROP CONSTRAINT CHK_PersonAge;
复制代码

对于 MySQL：

ALTER TABLE PersonsDROP 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 PersonsALTER City SET DEFAULT 'Sandnes';
复制代码

对于 SQL Server：

ALTER TABLE PersonsADD CONSTRAINT df_CityDEFAULT 'Sandnes' FOR City;
复制代码

对于 MS Access：

ALTER TABLE PersonsALTER COLUMN City SET DEFAULT 'Sandnes';
复制代码

对于 Oracle：

ALTER TABLE PersonsMODIFY City DEFAULT 'Sandnes';
复制代码

删除 DEFAULT 约束

要删除 DEFAULT 约束，请使用以下 SQL：

对于 MySQL：

ALTER TABLE PersonsALTER City DROP DEFAULT;
复制代码

对于 SQL Server / Oracle / MS Access：

ALTER TABLE PersonsALTER COLUMN City DROP DEFAULT;
复制代码

对于 SQL Server：

ALTER TABLE PersonsALTER COLUMN City DROP DEFAULT;
复制代码


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

最后

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


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


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

划线
评论
复制
发布于: 刚刚阅读数: 5

版权声明: 本文为 InfoQ 作者【小万哥】的原创文章。

原文链接:【http://xie.infoq.cn/article/c61bbcdb6c50802dea01b4f5e】。文章转载请联系作者。

MySQL数据库sql程序员后端开发
用户头像

小万哥

关注

代码如人生 2023-02-09 加入

编程爱好者

评论

发布
暂无评论
Copyright © 2023, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
SQL FOREIGN KEY 约束- 保障表之间关系完整性的关键规则_MySQL_小万哥_InfoQ写作社区