怀疑在表中的1-1关系 [英] Doubt in 1-1 relationship in table
问题描述
I just want to clear doubts about 1 to 1 relationship in sql. To understand i have created following example.
Please let me know is this the correct way to implement(1:1) relationship or not.
我的尝试：
What I have tried:
CREATE TABLE tbEmployee (
EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
EmployeeName VARCHAR(100)
);
GO
CREATE TABLE tbEmployeeIdentityDetails (
RecordId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
AadharNumber VARCHAR(20) NOT NULL,
EmployeeId INT UNIQUE FOREIGN KEY REFERENCES tbEmployee(EmployeeId)
);
GO
INSERT INTO tbEmployee (EmployeeName)
VALUES
('Aman'),
('Kapil'),
('Vijay'),
('Panjak');
GO
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987', 1),
('6432 3246 9097', 2),
('8875 8746 9234', 3),
('4678 4526 6654', 4);
GO
SELECT * FROM tbEmployee
SELECT * FROM tbEmployeeIdentityDetails
--On trying to insert another record for the same employee generates error e.g.
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987',4);
异常：违反UNIQUE KEY约束'UQ__tbEmploy__7AD04F10917285FA'。无法在对象'dbo.tbEmployeeIdentityDetails'中插入重复键。重复键值为（4）。
语句已被终止。
tbEmployeeIdentityDetails中每个员工只允许一条记录表是正确的。但我认为它应该遵循这个概念：一个aadhar号码只能属于一个1名员工，1个员工只能拥有一个aadhar号码，因为aadhar号码是印度每个员工的唯一ID
Exceptions: Violation of UNIQUE KEY constraint 'UQ__tbEmploy__7AD04F10917285FA'. Cannot insert duplicate key in object 'dbo.tbEmployeeIdentityDetails'. The duplicate key value is (4).
The statement has been terminated.
It is allowing only one record for each employees in tbEmployeeIdentityDetails table which is correct. But i think it should follow the concept: One aadhar number can belong to only one 1 employee and 1 employee can have only one aadhar number since aadhar number is the unique id for each employee in India
推荐答案
如果每个员工都有一个且只有一个Aadhar号码，为什么要将它存储在一个单独的表中呢？将它作为列添加到Employee表中，并使其成为UNIQUE和NOT NULL。如果每个员工只有一个值，那么在单独的表和关系中没有任何意义！
If every employee has one and only one Aadhar number, why are you storing it in a separate table at all? Add it as a column to the Employee table, and make that UNIQUE and NOT NULL as well. No point in a separate table and a relationship if there is only one value for each employee!
我完全赞同OG，我想指出你这篇文章：One-to-one（数据模型） - 维基百科 [ ^ ]，它详细解释了一对一的关系：
I completely agree with OG and i wanted to point you out to this article: One-to-one (data model) - Wikipedia[^], which explains a one-to-one relationship in details:
重要的是要注意，一对一的关系不是数据的属性，而是关系本身。 母亲及其子女的名单可能恰好描述了只有一个孩子的母亲，在这种情况下，母亲餐桌的一排只会指儿童餐桌的一排，反之亦然，但这种关系本身不是一对一的 ，...
It is important to note that a one-to-one relationship is not a property of the data, but rather of the relationship itself. A list of mothers and their children may happen to describe mothers with only one child, in which case one row of the mothers table will refer to only one row of the children table and vice versa, but the relationship itself is not one-to-one, ...
首先要做的事情。正如OG（Original Griff）所述， AadharNumber 需要应用唯一索引。如果它将保存在员工身份详细信息表中，那么您可以运行以下代码：
First things first. As OG (Original Griff) stated the AadharNumber would need to have a Unique Index applied to it. If it going to be kept in the Employee Identity Details table then you can run this code:
CREATE UNIQUE NONCLUSTERED INDEX [IX_EmployeeIdentity_AadharNumber]
ON dbo.tbEmployeeIdentityDetails ( AadharNumber)
GO
第二件事，正如OG质疑的那样;为什么这些在单独的表中？分割信息的唯一真正原因是行大小是否推动8K或者如果数据量导致性能问题。这种设计的负面影响是拥有额外表的开销，外键约束的性能影响（必须在第一个表上进行选择），然后需要获取所有数据的查询。关于FK Constraint的另一件事 - 除非你做复杂的星形加入查询，它们在性能方面几乎没有任何好处。
至于Employee表...如果您不打算将Aadhar添加到它，它将需要更多独特的信息。有些名字很常见，可能会发生碰撞。
现在，为了进行测试和学习，我们会保留原样，并假设在实际构建时真实的东西，你会按照上面的建议。
和我的建议。
我一般建议不要直接访问表格（例如插入，选择，编辑，删除）并更喜欢使用存储过程。通过两次INSERT查询，我注意到您并不总是保证发布的第二组ID与第一组匹配。如果这是通过程序完成的，这将消除app和数据库之间的往返，你甚至可以返回New Employee ID，如下所示：
Second thing, as OG questioned; why are these in separate tables? The only real reason to split the information up is if the row size is pushing 8K OR if amount of data is causing performance issues. The negatives of this design are the overhead of having an additional table, the performance impacts of the Foreign Key constraint (which essentialy has to do a select on the first table), and then there are the queries needed to get all the data. Another thing about the FK Constraint- unless you are doing complex star joined queries, they are of little to no benefit when it comes to performance.
As for the Employee table... if you are not going to add the Aadhar to it, it will need more unique information. Some names are quite common and a collision is likely.
Now, for just testing and learning, we'll leave things as they are and assume that when you actually build something real you will follow the above suggestions.
And my suggestions.
I generally recommend against "direct" table access (eg Insert, Select, Edit, Delete) and prefer to use Stored Procedures. With the two INSERT queries you had I noted that you aren't always guaranteed that the second set IDs posted will match the first. If this was done via a program this would eliminate round trips between the app and database with this, and you could even return the New Employee ID like this:
CREATE PROCEDURE dbo.EmployeeAndDetails_Create (
@Name NVARCHAR(32),
@Aadhar NVARCHAR(14)
) AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID INT = -1
INSERT tbEmployee (EmployeeName)
VALUES (@Name)
SET @EmpID = Scope_Identity()
INSERT tbEmployeeIdentityDetails (EmployeeId, AadharNumber)
VALUES (@EmpID, @Aadhar)
SELECT NewEmployeeID = @EmpID
END
GO
这篇关于怀疑在表中的1-1关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
