经审核的表和外键

Question

我有一个包含多个表的数据库，这些表必须经过审计。

例如，我有一个对象表，它定义了一个唯一的ID、一个名称和一个描述。

名称将始终相同。不可能更新它；ObjectA"；将永远是"；ObjectA"；。

正如您所看到的，名称在数据库中不是唯一的，而仅在逻辑中是唯一的。

行"；从"至"；以及"；creator_id"；用于审核更改"从"；是变更的日期；至"；是添加新行的日期，当它是最后一行时为null"creator_id"；是进行更改的用户的ID。

+----+----------+--------------+----------------------+----------------------+------------+
| id |   name   | description  |         from         |          to          | creator_id |
+----+----------+--------------+----------------------+----------------------+------------+
|  1 | ObjectA  | My object    | 2021-05-30T00:05:00Z | 2021-05-31T05:04:36Z |         18 |
|  2 | ObjectB  | My desc      | 2021-05-30T02:07:25Z | null                 |         15 |
|  3 | ObjectA  | Super object | 2021-05-31T05:04:36Z | null                 |         20 |
+----+----------+--------------+----------------------+----------------------+------------+

现在我有了另一个表，该表必须具有该对象表的外键；独特的"；对象名称。

+----+---------+-------------+
| id |   foo   | object_name |
+----+---------+-------------+
|  1 | blabla  | ObjectA     |
|  2 | wawawa  | ObjectB     |
+----+---------+-------------+

如何在这两个表之间创建此链接？

我已经尝试用uuid创建另一个表，并添加一列"；unique_identifier"；在对象表中。然后外键将链接到此uuid表，而不是对象表。问题是，我有多个表有这个问题，我将不得不创建双倍数量的表。

也可以使用对象ID作为FK而不是名称，但这意味着在更新对象时，必须使用新ID更新具有该FK的每个表。

Answer 1

根据SQL标准，外键必须引用父表的主键或唯一键。如果主键有多列，则外键必须具有相同的列数和列顺序。因此，外键引用父表中唯一的一行；不可能有重复。

另一个解决方案是使用触发器，您可以在插入另一个表之前检查对象表中对象的存在。

更新：添加代码

准备表格并创建触发器：(为了简单起见，我在Objects表格中只包含了3列。在触发器中，我只是在其他部分打印错误，您可以使用RAISEERROR函数引发错误，将错误返回给客户端(

Create table AuditObjects(id int identity (1,1),ObjectName varchar(20), ObjectDescription varchar(100) )
Insert into AuditObjects values('ObjectA','description ObjectA Test')
Insert into AuditObjects values('ObjectB','description ObjectB Test')
Insert into AuditObjects values('ObjectC','description ObjectC Test')
Insert into AuditObjects values('ObjectB','description ObjectB Test')
Insert into AuditObjects values('ObjectB','description ObjectB Test')
Insert into AuditObjects values('ObjectA','description ObjectA Test')

Create table ObjectTab2 (id int identity (1,1),foo varchar(200), ObjectName varchar(20))
go

CREATE TRIGGER t_CheckObject ON ObjectTab2 INSTEAD OF INSERT
AS BEGIN

Declare @errormsg varchar(200), @ObjectName varchar(20)
select @ObjectName = objectname from INSERTED
if exists(select 1 from AuditObjects where objectname = @ObjectName)
Begin
INSERT INTO ObjectTab2 (foo, Objectname)
Select foo, Objectname
from INSERTED
End
Else
Begin
Select @errormsg = 'Object '+objectname+ ' does not exists in AuditObjects table'
from Inserted

print(@errormsg)
End

END;

现在，如果您尝试在ObjectTab2中插入对象名称为"的行；ObjectC"；，插入将被允许为"插入"；对象C"；存在于审核表中。

Insert into ObjectTab2 values('blabla', 'ObjectC')
Select * from ObjectTab2
id          foo    ObjectName
----------- ------ --------------------
1           blabla ObjectC

然而，如果您尝试输入"；ObjectD"；，它不会进行插入并在输出中给出错误消息。

Insert into ObjectTab2 values('Inserting ObjectD', 'ObjectD')
Object ObjectD does not exists in AuditObjects table

这不是你想要的，而是给你同样的功能和结果。

Answer 2

您是否可以不继续基于"对象名称"链接这两个表。唯一的区别是，当您连接这两个表时，您将从表1(您引用的第一个表(中获得多条记录。然后，您可以根据自己的要求，添加基于from和to的筛选条件。

发布编辑-我的意思是，在这种情况下，您仍然可以在不引入外键的情况下获得所需的结果-

让我们将您的表称为表1和表2

--Below will give you all records from Table1
SELECT T2.*, T1.description, T1.creator_id, T1.from, T1.to 
FROM TABLE2 T2 
INNER JOIN TABLE1 T1 ON T2.OBJECT_NAME = T1.NAME;
--Below will give you ONLY those records from Table1 whose TO is null
SELECT T2.*, T1.description, T1.creator_id, T1.from, T1.to 
FROM TABLE2 T2 
INNER JOIN TABLE1 T1 ON T2.OBJECT_NAME = T1.NAME
WHERE T1.TO IS NULL;

Answer 3

我决定使用一个额外的表来进行最终设计：

表"对象"；

+-------+--------------------------------------+---------+--------------+----------------------+----------------------+------------+
| id PK |            identifier FK             |  name   | description  |         from         |          to          | creator_id |
+-------+--------------------------------------+---------+--------------+----------------------+----------------------+------------+
|     1 | 123e4567-e89b-12d3-a456-426614174000 | ObjectA | My object    | 2021-05-30T00:05:00Z | 2021-05-31T05:04:36Z |         18 |
|     2 | 123e4567-e89b-12d3-a456-524887451057 | ObjectB | My desc      | 2021-05-30T02:07:25Z | null                 |         15 |
|     3 | 123e4567-e89b-12d3-a456-426614174000 | ObjectA | Super object | 2021-05-31T05:04:36Z | null                 |         20 |
+-------+--------------------------------------+---------+--------------+----------------------+----------------------+------------+

表"对象标识符">

+--------------------------------------+
|              identifier PK           |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426614174000 |
| 123e4567-e89b-12d3-a456-524887451057 |
+--------------------------------------+

表"foo"；

+-------+--------+--------------------------------------+
| id PK |  foo   |         object_identifier FK         |
+-------+--------+--------------------------------------+
|     1 | blabla | 123e4567-e89b-12d3-a456-426614174000 |
|     2 | wawawa | 123e4567-e89b-12d3-a456-524887451057 |
+-------+--------+--------------------------------------+