MySQL问题-唯一键无法正常运行，还是我误会了? [英] MySQL Question - Unique Key Not functioning correctly, or am I misunderstanding?

问题描述

我正在尝试创建一个可以包含四个不同部分中的任何一个的关系，但是相同部分的任何集合都应视为唯一.

I'm trying to create a relation where any of four different parts may be included, but any collection of the same parts should be handled as unique.

示例: 分配必须具有分配的公司，可以有选择地分配位置，工作组和程序. 分配可能没有没有位置的工作组.

Example: An assignment must have an assigned company, may optionally have an assigned location, workgroup and program. An assignment may not have a workgroup without a location.

假设我们拥有公司A，B，C；位置X，Y，Z；工作组I，J，K和程序1,2,3.

Let's assume we have companies A, B, C; locations X, Y, Z; workgroups I, J, K and programs 1, 2, 3.

因此有效关系可以包括 A-X-I-1 A-Z-2 经过 C C-3 B-Z-K

So valid relations could include A - X - I - 1 A - Z - 2 B - Y C C - 3 B - Z - K

但是无效关系将包括 A-K(无位置的工作组) Y-K-1(无公司)

But invalid relations would include A - K (Workgroup without location) Y - K - 1 (No company)

因此，为了创建我的表，我创建了

So, to create my table, I've created

companyID INT NOT NULL,
FOREIGN KEY companyKEY (companyID) REFERENCES company (companyID),
locationID INT,
FOREIGN KEY locationKEY (locationID) REFERENCES location (locationID),
workgroupID INT,
FOREIGN KEY workgroupKEY (workgroupID) REFERENCES workgroup (workgroupID),
programID INT,
FOREIGN KEY programKEY (programID) REFERENCES program (programID),
UNIQUE KEY companyLocationWorkgroupProgramKEY (companyID, locationID, workgroupID, programID)

我认为，如果有一个工作组(我可以很高兴地通过编程或使用触发器来完成工作)，那么除了可以分配一个位置之外，这还可以处理我的所有关系.

I figure this would handle all my relations besides the neccessity of an assignment to have a location if there is a workgroup (which I can happily do programatically or with triggers, I think)

但是，当我测试此架构时，它允许我输入以下内容...

However, when I test this schema, it allows me to enter the following...

INSERT INTO test VALUES (1, null, null, null), (1, null, null, null);

...无怨言.我猜(1，null，null，null)不等于本身，因为包含了null.如果是这样，我有什么办法可以处理这种关系?

...without complaint. I'm guessing that (1, null, null, null) does not equal itself because nulls are included. If this is the case, is there any way I can handle this relation?

任何帮助将不胜感激！

推荐答案

这是一项功能(尽管不是我所期望的).

This is a Feature (though not what I expected, either).

此线程建议将您的密钥设置为主键，以实现您期望的行为:

This thread suggests making your key a Primary key to get the behavior you expected:

这是一个功能-NULL值是 未定义的值，因此为两个NULL 值不一样.可以是 有点混乱，但是什么时候有意义 你考虑一下.

This is a feature - a NULL value is an undefined value, therefore two NULL values are not the same. Can be a little confusing but makes sense when you think about it.

唯一索引确实可以确保 非NULL值是唯一的；你可以 指定您的列不接受 NULL值.

A UNIQUE index does ensure that non-NULL values are unique; you could specify that your column not accept NULL values.

这篇关于MySQL问题-唯一键无法正常运行，还是我误会了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！