MySQL 中外键关联的不是主键如何调整

我有一个包含数据的表，其中一行需要存在于另一个表中。所以我需要一个外键来保持参照完整性。

CREATE TABLE table1
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
AnotherID INT NOT NULL,
SomeData VARCHAR(100) NOT NULL
)


CREATE TABLE table2
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
AnotherID INT NOT NULL,
MoreData VARCHAR(30) NOT NULL,


CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)
)

但是，正如您所看到的，表 I 的外键不是 PK。有没有创建这个外键的方法，或者更好的方法来维护这个参照完整性？

212685 次浏览
小开
最佳答案

如果您真的想为非主键创建一个外键，那么它必须是一个对其具有唯一约束的列。

来自 在线图书:

FOREIGNKEY 约束不必只链接到主要成员 另一个表中的 KEY 约束; 也可以定义为引用 另一个表中 UNIQUE 约束的列。

因此，在您的情况下，如果您使 AnotherID唯一，它将被允许。如果您不能应用一个独特的约束，那么您就不走运了，但是如果您仔细想想，这确实是有意义的。

不过，如前所述，如果您有一个非常好的主键作为候选键，为什么不使用它呢？

小开

正如其他人指出的那样，在理想情况下，外键将被创建为对主键(通常是 IDENTITY 列)的引用。然而，我们并不生活在一个理想的世界中，有时甚至对模式的一个“小”更改都可能对应用程序逻辑产生显著的涟漪效应。

假设一个 Customer 表有一个 SSN 列(和一个哑主键) ，还有一个 Claim 表也包含一个 SSN 列(由 Customer 数据的业务逻辑填充，但不存在 FK)。该设计存在缺陷，但已经使用了几年，并且已经在模式上构建了三个不同的应用程序。显而易见，删除 Claum.SSN 并建立一个真正的 PK-FK 关系将是理想的，但也将是 意义重大的彻底改革。另一方面，在 Customer.SSN 上放置一个 UNIQUE 约束，并在 solum.SSN 上添加一个 FK，可以提供参照完整性，对应用程序的影响很小或没有影响。

别误会，我完全赞成正常化，但有时实用主义胜过理想主义。如果一个平庸的设计可以用创可贴来帮助，手术可能是可以避免的。

小开

巫师。
我假设当某人在这里着陆时，他需要一个外键来在包含非唯一键的表中列。

问题是，如果您遇到这个问题，那么数据库模式将被非规范化。 < br/>

例如，在表中保存房间，使用 room-uid 主键、 DateFrom 和 DateTo 字段，以及另一个 uid，这里是 RM _ ApertureID，用于跟踪同一个房间，还有一个软删除字段，比如 RM _ Status，其中99表示“已删除”，而 < > 99表示“活动”。

因此，在创建第一个房间时，插入 RM _ UID 和 RM _ ApertureID 作为与 RM _ UID 相同的值。 然后，当您终止房间到一个日期，并使用一个新的日期范围重新建立它时，RM _ UID 是 newid () ，前一个条目中的 RM _ ApertureID 成为新的 RM _ ApertureID。

因此，如果是这种情况，RM _ ApertureID 是一个非唯一字段，因此不能在另一个表中设置外键。

而且没有办法为非唯一列/索引设置外键，例如在 T _ ZO _ REM _ AP _ Raum _ Reinigung (WHERE RM _ UID 实际上是 RM _ ApertureID)中。
但是，为了禁止无效的值，您需要设置一个外键，否则，数据垃圾的结果是早而不是晚..。

现在，您可以在这种情况下(除了重写整个应用程序之外)插入一个 CHECK 约束，使用一个标量函数检查键的存在:

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]'))
ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_Constaint_ValidRmApertureId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_Constaint_ValidRmApertureId]
GO








CREATE FUNCTION [dbo].[fu_Constaint_ValidRmApertureId](
@in_RM_ApertureID uniqueidentifier
,@in_DatumVon AS datetime
,@in_DatumBis AS datetime
,@in_Status AS integer
)
RETURNS bit
AS
BEGIN
DECLARE @bNoCheckForThisCustomer AS bit
DECLARE @bIsInvalidValue AS bit
SET @bNoCheckForThisCustomer = 'false'
SET @bIsInvalidValue = 'false'


IF @in_Status = 99
RETURN 'false'




IF @in_DatumVon > @in_DatumBis
BEGIN
RETURN 'true'
END




IF @bNoCheckForThisCustomer = 'true'
RETURN @bIsInvalidValue




IF NOT EXISTS
(
SELECT
T_Raum.RM_UID
,T_Raum.RM_Status
,T_Raum.RM_DatumVon
,T_Raum.RM_DatumBis
,T_Raum.RM_ApertureID
FROM T_Raum
WHERE (1=1)
AND T_Raum.RM_ApertureID = @in_RM_ApertureID
AND @in_DatumVon >= T_Raum.RM_DatumVon
AND @in_DatumBis <= T_Raum.RM_DatumBis
AND T_Raum.RM_Status <> 99
)
SET @bIsInvalidValue = 'true' -- IF !


RETURN @bIsInvalidValue
END






GO






IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]'))
ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
GO




-- ALTER TABLE dbo.T_AP_Kontakte WITH CHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung WITH NOCHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
CHECK
(
NOT
(
dbo.fu_Constaint_ValidRmApertureId(ZO_RMREM_RM_UID, ZO_RMREM_GueltigVon, ZO_RMREM_GueltigBis, ZO_RMREM_Status) = 1
)
)
GO




IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]'))
ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung CHECK CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
GO
小开

主键始终需要是唯一的，如果表是一对多关系，则外键需要允许非唯一值。如果表是由一对一关系而不是一对多关系连接的，那么使用外键作为主键是完全可以的。

FOREIGNKEY 约束不必只链接到另一个表中的 PRIMARYKEY 约束; 还可以定义它来引用另一个表中 UNIQUE 约束的列。

小开

是的，你通常至少会索引它。

create table student(
id int,
name varchar(30),
index inName(id)
);


CREATE TABLE grade(
id int,
subject varchar(30),
mark double,
foreign key(id) references student(id)
);

提交答案