PostgreSQL Foreign Key


PostgreSQL Foreign Key

在本节中,我们将了解PostgreSQL Foreign Key的工作原理,PostgreSQL Foreign Key示例**,**如何使用Foreign Key约束将 PostgreSQL Foreign Key添加到表中。

什么是 PostgreSQL Foreign Key/Foreign Key约束?

一个Foreign Key是一组列具有取决于值主键从另一个表的好处。它用于将一列或一组列中的值显示在同一列或另一表中的列组合中。

Foreign Key也被称为引用的键,而且主键字段从另一个表,这意味着在一个表的Foreign Key字段指的是其他表的主键字段匹配。

在PostgreSQL 中,Foreign Key的值与另一个表中主键的实际值平行;这就是为什么它也被称为参照完整性约束

PostgreSQL Foreign Key约束指定子表中一组列或一列中的值,相当于父表中一组列或一列中的值。

换句话说,我们可以说Foreign Key可以生成与表的父子关系

在父子关系中,父表保留初始列值子表的列值引用父列值。

注意:一个 PostgreSQL 表可以有各种Foreign Key,这取决于它与其他表的连接。PostgreSQL 允许我们使用Foreign Key约束来描述Foreign Key。

如何在 PostgreSQL 中创建Foreign Key

在 PostgreSQL 中,我们可以借助以下命令创建Foreign Key:

  • CREATE TABLE command
  • ALTER TABLE command

注意:在 PostgreSQL 中,ALTER TABLE 命令用于添加或删除主键。

现在,我们正在详细讨论每一个。

使用 CREATE TABLE 命令创建Foreign Key

我们可以使用 CREATE TABLE 命令的帮助在 PostgreSQL 中创建Foreign Key。

创建Foreign Key的语法

下图用于在 PostgreSQL 中使用CREATE TABLE命令定义Foreign Key:

[CONSTRAINT constraint_name]    
FOREIGN KEY [foreign_key_name] (column_name, ...)    
REFERENCES parent_table_name (column_name,...)    
[ON DELETE referenceOption]    
[ON UPDATE referenceOption]

在上述语法中,我们使用了以下参数,我们在下表中讨论了这些参数:

参数说明

参数名称 描述
Constraint_name 约束名称参数用来定义Foreign Key约束如果我们不指定约束名称,PostgreSQL 将自动创建约束名称。
Column_name 它用于指定我们要在其中创建Foreign Key的列名。并且应该写在FOREIGN KEY关键字之后的括号中。
Parent_table 父表参数定义父表的名字,其次是列名,它引用Foreign Key列。
Refrence_option 用于定义ON DELETE 和 ON UPDATE 中的删除和更新操作它还使用和子表之间的(ON DELETE 和 ON UPDATE)子句验证Foreign Key是否保持参照完整性。

在PostgreSQL中,我们有五种不同的引用选项,分别如下:

  • SET DEFAULT
  • SET NULL
  • CASCADE
  • NO ACTION
  • RESTRICT

让我们在下表中一一查看五种不同的参考选项的简要介绍:

参考选项 解释
SET DEFAULT PostgreSQL 解析器识别SET DEFAULT尽管如此,InnoDB 和 NDB表都排除了 SET DEFAULT 操作。
SET NULL SET NULL引用选项用于当我们卸下或从修改任何行子表中的Foreign Key列值设置为NULL
CASCADE 如果我们想从表中删除或修改任何行,那么我们将使用CASCADE**子**表中相应行的值将被重复删除或更新。
RESTRICT 制约作用,当我们删除或修改从父表,其具有在基准(类似的行的任何行用于)表。
NO ACTION NO ACTION参考作用类似于RESTRICT但它有一个区别,即在我们尝试更新特定表后验证参照完整性

使用 Create 命令的 PostgreSQL Foreign Key示例

要了解PostgreSQL Foreign Key的工作原理我们将看到以下示例,该示例描述了 PostgreSQL 中如何使用Foreign Key。

在下面的命令中,我们将使用CREATE命令的帮助创建两个表作为EmployeeDepartment并使用INSERT命令插入一些值。

要将Employee创建到Javatpoint 数据库中,我们将使用CREATE命令。

在创建Employee表之前,如果Javatpoint数据库中存在类似的表,我们将使用DROP TABLE命令。

DROP TABLE IF EXISTS Employee;

输出

执行上述命令后,我们会得到如下窗口信息,显示Employee\表已经删除成功。

PostgreSQL Foreign Key

删除之前创建的Employee\表后,我们将创建一个新的Employee\表,其中包含各种列,例如Employee_id、Employee_name列。

在此,Employee_id列是Generated Always as identity 约束

CREATE TABLE Employee(  
   Employee_id INT GENERATED ALWAYS AS IDENTITY,  
   Employee_name VARCHAR(50) NOT NULL,  
   PRIMARY KEY(Employee_id)  
);

输出

执行上述命令时,我们将收到以下消息:Employee\表已成功创建到Javatpoint数据库中。

PostgreSQL Foreign Key

创建Employee表后,我们将在CREATE命令的帮助下将名为Department\表的第二个表创建到Javatpoint 数据库中

在创建Department表之前,我们将使用DROP TABLE命令来确保Javatpoint数据库中不存在类似的表。

DROP TABLE IF EXISTS Department;

输出

执行完上面的命令后,我们会得到如下窗口信息: The Department\ has been created successfully from the Javatpoint database。

PostgreSQL Foreign Key

一旦系\表已被删除,我们将创建一个新的部门\包括各列如表DEPARTMENT_ID,DEPARTMENT_NAME列。

CREATE TABLE Department(  
Department_id INT GENERATED ALWAYS AS IDENTITY,  
Employee_id INT,  
Department_name VARCHAR(200) NOT NULL,  
PRIMARY KEY(Department_id),  
CONSTRAINT fk_Employee  
FOREIGN KEY(Employee_id)   
REFERENCES Employee(Employee_id)  
);

输出

执行上述命令时,我们将收到以下消息:Department\表已成功创建到Javatpoint数据库中。

PostgreSQL Foreign Key

在上面的例子中,雇员\表是父表,系\表是子表。每个员工有零个或多个部门,每个部门属于零个或一个员工。

Department\表中的Employee_id列是Foreign Key列,它引用了Employee\表中具有相似名称的主键列。

在下面的语句中,Foreign Key约束fk_Employee系\表指定EMPLOYEE_ID作为Foreign Key:

CONSTRAINT fk_Employee  
FOREIGN KEY(Employee_id)   
REFERENCES Employee(Employee_id)

无操作是默认选项,因为Foreign Key约束不具有ON DELETEON UPDATE动作

NO ACTION

我们将在INSERT命令对NO ACTION选项的帮助下将一些记录插入到(Employee 和 Department\)表中。

INSERT INTO Employee(Employee_name)  
VALUES('John Smith'),  
('Michael Brown');

输出

执行上述命令后,我们会得到如下消息窗口,显示两个值已成功插入到Employee\表中。

PostgreSQL Foreign Key

Employee表中插入值后,我们将使用Insert命令将一些记录输入到Department\表中,如下面的命令所示:

INSERT INTO Department(Employee_id, Department_name)  
VALUES(1,'Accounting'),  
(1,'Accounting'),  
(2,'Human Resource'),  
(2, 'Human Resource-1');

输出

执行上述命令后,我们将得到如下消息窗口,显示四行已插入到Department\表中。

PostgreSQL Foreign Key

在下面的命令中,我们使用DELETE命令从Employee\表中删除Employee_id 1

DELETE FROM Employee  
WHERE Employee_id = 1;

输出

执行上述命令后,由于ON DELETE NO ACTION,PostgreSQL 发出以下错误

Error: update or delete on table "employee" violates foreign key constraint "fk_employee" on table "department".  
DETAIL: Key (employee_id)=(1) is still referenced from table "department".  
SQL state: 23503

PostgreSQL Foreign Key

换句话说,我们可以说 PostgreSQL 发出了约束冲突,因为Employee id=1的引用行仍然存在于Department\表中。

RESTRICT

RESTRICT作用类似于NO ACTION。唯一的区别是当我们使用INITIALLY DEFERRED 或 INITIALLY IMMEDIATE模式将Foreign Key约束指定为DEFERRABLE时出现。

SET NULL

了解NO ACTION 和 RESTRICT选项的工作后,我们将了解带有Foreign Key的SET NULL操作的工作。

在下面的命令中,我们将创建两个名为Staff\Sector 的\新表。\

但是,首先,如果Javatpoint数据库中已经存在类似的表,我们将使用DROP TABLE命令。

然后,我们将使用Foreign Key创建这些表,它们在ON DELETE子句中具有SET NULL操作:

删除表

DROP TABLE IF EXISTS Staff;

输出

执行上述命令后,我们将得到以下窗口消息:Staff\表不存在。

PostgreSQL Foreign Key

现在,我们将在DROP table命令的帮助下删除Sector\表。

DROP TABLE IF EXISTS Sector;

输出

执行上述命令后,我们会得到如下消息窗口: The Sector\ table has does not exist。

PostgreSQL Foreign Key

使用 ON DELETE 子句中的 SET NULL 选项创建表

CREATE TABLE Staff(  
   Staff_id INT GENERATED ALWAYS AS IDENTITY,  
   Staff_name VARCHAR(50) NOT NULL,  
   PRIMARY KEY(Staff_id)  
);

输出

职员\表已执行上面的命令后创建,因为我们可以在下面的截图中看到:

PostgreSQL Foreign Key

一旦职员\表已经创建成功,我们将创建部门\使用表CREATE命令,并使用SET NULL在动作ON DELETE子句:

CREATE TABLE Sector(  
Sector_id INT GENERATED ALWAYS AS IDENTITY,  
Staff_id INT,  
Sector_name VARCHAR(200) NOT NULL,  
PRIMARY KEY(Sector_id),  
CONSTRAINT fk_Staff  
FOREIGN KEY(Staff_id)   
REFERENCES Staff(Staff_id)  
ON DELETE SET NULL  
);

输出

执行上述命令后,我们将得到以下消息窗口,显示Sector\表已成功创建到Javatpoint数据库中。

PostgreSQL Foreign Key

一旦我们成功创建了Staff\Sector\表,我们将使用INSERT命令向其中插入一些值。

INSERT INTO Staff(Staff_name)  
VALUES('Sophia Smith'),  
('Jessica Williams'),  
('Victoria Lopez');

输出

执行上述命令后,我们将得到以下消息窗口,显示三个值已成功插入到Staff\表中。

PostgreSQL Foreign Key

Staff\表中插入值后,我们将在 INSERT 命令的帮助下向Sector\表中输入一些记录,如下所示:

INSERT INTO Sector(Staff_id, Sector_name)  
VALUES(1,'Nursing'),  
(1,'Nursing'),  
(2,'Medication'),  
(2, 'Medication'),  
(3,'Accounting');

输出

执行上述命令后,我们将得到以下消息窗口,其中显示五行已插入扇区\表。

PostgreSQL Foreign Key

将行成功插入两个表后,我们将看到SET NULL是如何工作的。

为此,我们使用DELETE命令从Staff\表中删除了staff_id=2,如下所示:

DELETE FROM Staff  
WHERE Staff_id = 2;

输出

执行上述命令后,我们将得到以下消息窗口,显示特定Staff_id已成功删除。

PostgreSQL Foreign Key

由于ON DELETE SET NULL操作,子(扇区)表中的引用行设置为NULL

在下面的命令中,我们将使用SELECT命令查看Sector\表中的数据:

SELECT * FROM Sector;

输出

实现上述后SELECT命令,我们将得到下面的输出,其中显示的是与行Staff_id = 2扇区\表自动设置为NULL由于ON UPDATE SET NULL动作。

PostgreSQL Foreign Key

SET DEFAULT

ON DELETE SET DEFAULT是用来设置的标准值,在引用行的Foreign Key列子表从被引用行时,父表中被删除。

CASCADE

ON DELETE CASCADE选项删除的所有引用行子表(部)的被引用行时自动父表(employee)被移除。

注意:在 PostgreSQL Foreign Key中,我们将最常使用 ON DELETE CASCADE 选项。

让我们看一个例子来了解CASCADE选项如何与Foreign Key一起工作

在下面的命令中,我们将重新创建Employee 和 Department\表。

在以下示例中,我们将删除示例表(员工和部门)\并使用Foreign Key重新创建它们,该Foreign Key在ON DELETE子句中使用CASCADE操作:

删除表

DROP TABLE IF EXISTS Employee;

输出

执行上述命令后,我们会得到如下消息窗口:Employee\表已删除成功。

PostgreSQL Foreign Key

一旦Employee\表被删除,我们将在DROP table 命令的帮助下删除Department\表。

DROP TABLE IF EXISTS Department;

输出

执行上述命令后,我们会得到如下消息窗口:Department\表已删除成功。

PostgreSQL Foreign Key

重新创建表

成功删除两个表后,我们将使用Foreign Key重新创建这些表,该Foreign Key在ON DELETE子句中使用CASCADE操作。但是,fk_emloyee的删除操作更改为CASCADE

现在,我们将使用 CREATE table 命令重新创建Employee\表,如以下命令所示:

CREATE TABLE Employee(  
   Employee_id INT GENERATED ALWAYS AS IDENTITY,  
   Employee_name VARCHAR(50) NOT NULL,  
   PRIMARY KEY(Employee_id)  
);

输出

执行上述命令后,我们将得到以下消息:Employee\表已成功重新创建到Javatpoint数据库中。

PostgreSQL Foreign Key

创建Employee\表后,我们将在CREATE命令的帮助下将第二个表,即Department\表创建到Javatpoint 数据库中

CREATE TABLE Department(  
Department_id INT GENERATED ALWAYS AS IDENTITY,  
Employee_id INT,  
Department_name VARCHAR(200) NOT NULL,  
PRIMARY KEY(Department_id),  
CONSTRAINT fk_Employee  
FOREIGN KEY(Employee_id)   
REFERENCES Employee(Employee_id)  
ON DELETE CASCADE  
);

输出

执行上述命令时,我们将收到以下消息:Department\表已成功重新创建到Javatpoint数据库中。

PostgreSQL Foreign Key

在重新创建Employee\Department\表后,我们将使用INSERT命令向这两个表中插入几条记录。

INSERT INTO Employee(Employee_name)  
VALUES('John Smith'),  
('Michael Brown');

输出

我们将得到如下消息窗口,显示执行上述命令后,两个值已成功插入到Employee\表中。

PostgreSQL Foreign Key

Employee表中插入值后,我们将使用Insert命令将一些记录输入到Department\表中,如下面的命令所示:

INSERT INTO Department(Employee_id, Department_name)  
VALUES(1,'Accounting'),  
(1,'Accounting'),  
(2,'Human Resource'),  
(2, 'Human Resource');

输出

执行上述命令后,我们将得到如下消息窗口,显示四行已插入到Department\表中。

PostgreSQL Foreign Key

重新创建并将重新插入两个表后,我们将使用DELETE命令。

在下面的例子中,我们将删除EMPLOYEE_ID = 2雇员\表。

DELETE FROM Employee  
WHERE Employee_id = 2;

输出

在执行上述命令时,我们将得到以下消息窗口,其中显示特定的employee_id值已从Employee\表中成功删除。

PostgreSQL Foreign Key

所有的引用行系\表会被自动删除,因为我们已经使用了ON DELETE CASCADE在行动部\表。

换句话说,我们可以说,如果我们删除Employee\表中的员工记录,那么Department\表中的相关记录也被自动删除了。

要检查上面的ON DELETE CASCADE操作是否正常工作,我们将使用 SELECT 命令,如下面的语句所示:

SELECT * FROM Department;

输出

执行上述命令后,我们将得到以下输出:

PostgreSQL Foreign Key

如果我们向现有表添加带有ON DELETE CASCADE选项的Foreign Key约束,我们将需要执行以下步骤:

步骤1

首先,我们将使用下图删除现有的Foreign Key约束:

ALTER TABLE child_table  
DROP CONSTRAINT constraint_fkey;

第2步

之后,我们将借助以下语法添加一个带有ON DELETE CASCADE操作的新Foreign Key约束:

ALTER TABLE child_table  
ADD CONSTRAINT constraint_fk  
FOREIGN KEY (fk_columns)  
REFERENCES parent_table(parent_key_columns)  
ON DELETE CASCADE;

使用 ALTER TABLE 命令创建Foreign Key

我们可以使用ALTER TABLE命令的帮助在 PostgreSQL 中生成Foreign Key。

ALTER TABLE命令用于执行换入现有的表。有时,我们也可以为当前表列添加Foreign Key。

使用 ALTER 表语句创建Foreign Key的语法

下图显示了在现有表中添加Foreign Key的ALTER TABLE语句:

ALTER TABLE child_table   
ADD CONSTRAINT [constraint_name]   
FOREIGN KEY (fk_columns)   
REFERENCES parent_table (parent_key_columns);

注意:如果我们要使用 ALTER TABLE 命令添加Foreign Key,建议在Foreign Key引用的不同列上创建索引。

使用 ALTER TABLE 命令的 PostgreSQL Foreign Key示例

要了解PostgreSQL Foreign Key的工作原理我们将看到以下示例,该示例描述了如何在 PostgreSQL 中通过 ALTER table 命令添加Foreign Key。

在下面的示例中,我们在Javatpoint数据库中创建了两个名为Clients\Company 的\新表,该表不包含表描述中的Foreign Key列

要创建Clients 和 Company\表,我们将使用CREATE table 命令,如下面的语句所示:

CREATE TABLE Clients (    
Client_ID INT NOT NULL,    
Client_Name varchar(55) NOT NULL,    
Client_location varchar(55) NOT NULL,    
PRIMARY KEY (Client_ID)    
);

输出

执行上述命令后,我们将得到以下消息窗口;在客户\表已成功创建成Javatpoint\数据库。

PostgreSQL Foreign Key

一旦客户\表已经产生,我们将创建子表(公司)使用CREATE TABLE命令:

CREATE TABLE Company (    
  Company_id INT,    
  Client_Id INT,    
  Company_details varchar(50) NOT NULL,    
  Company_type varchar(50) NOT NULL    
);

输出

执行上述命令后,我们将得到以下消息窗口,显示Company表已成功创建到类似的数据库中。

PostgreSQL Foreign Key

成功创建parent (Clients)表和child( Company)表后,如果我们向现有表添加Foreign Key,我们可以执行以下 ALTER TABLE 命令:

ALTER TABLE Company ADD CONSTRAINT fk_Clients    
FOREIGN KEY ( Company_Id )REFERENCES Clients(Client_ID)   
ON DELETE CASCADE   
ON UPDATE RESTRICT;

输出

执行上述命令后,我们将得到以下消息窗口,显示特定表已成功更改为类似的数据库。

PostgreSQL Foreign Key

如何删除 PostgreSQL Foreign Key

在 PostgreSQL 中,我们可以使用 ALTER TABLE 命令的帮助从现有表中删除Foreign Key。

使用 alter table 命令删除Foreign Key的语法

下图用于在 PostgreSQL 中使用 ALTER TABLE 命令的帮助删除Foreign Key:

ALTER TABLE table_name   
DROP CONSTRAINT constraint_fkey;

在上述语法中,我们使用了以下参数,我们在下表中讨论了这些参数:

范围 描述
table_name 表名参数用于指定我们要从中删除Foreign Key的表名。
constraint_Fkey 它用于定义创建表时添加的Foreign Key名称。

使用 ALTER TABLE 命令删除 PostgreSQL Foreign Key的示例

让我们看一个从定义的表中删除Foreign Key的示例示例。

为此,我们使用上面新创建的Employee\表并使用ALTER TABLE命令的帮助删除Foreign Key,如下面的语句所示:

ALTER TABLE Department  
DROP CONSTRAINT fk_employee;

输出

执行上述命令后,我们将得到以下消息窗口,显示Foreign Key已成功从特定表中删除。

PostgreSQL Foreign Key

概述

PostgreSQLForeign Key部分,我们学习了以下主题:

  • 我们已经使用CREATE TABLE命令为特定表创建Foreign Key
  • 我们已经理解了使用CREATE TABLE**改变**PostgreSQL Foreign Key的概念
  • 我们还看到了引用操作选项的示例:SET NULL、NO ACTION、CASCADE、RESTRICT 和 SET DEFAULT。
  • 我们已经使用ALTER TABLE命令从特定表中删除Foreign Key