我们如何基于ERD基数对外键列施加约束? [英] How do we give constraint on foreign key column based on ERD cardinality?
问题描述
比方说，我有2张桌子，如下所示:
部门:
DeptId名称 员工:
EmpId名称DeptId 在这里，我在部门和 Employee 表之间存在0对多的关系.
现在，当我读取从部门到 Employee 的关系时，它说1个部门可以有0个或更多员工.
这是否意味着在创建 Employee 表时，由于零个或多个关系，我会将 DepartmentId (外键)设置为null?
我们如何表示1个或多个关系，例如，如果ERD中的关系说1个部门可以有0个或更多员工，则 DepartmentId (外键)在 Employee中将不能为空表格?
基本上，我只是想了解我们如何基于ERD基数(1对多的关系，1对零个或多个关系)对外键施加约束.
任何人都可以帮助我消除这种混乱吗?我真的会很感激:)
ERD工具很棒，但是最强大的设计工具仍然是自然语言，谓词和约束.因此，如果有疑问，请使用纯文本编辑器而不是ERD工具.另外，在设计阶段也不允许NULL.
所有属性(列)非空[p x] =谓词x(c x.y)=约束x.yPK =主键AK =备用密钥(唯一)FK =外键 从部门开始，这部分不会改变.
存在 [p 1] ，该部门由编号 DEPT_ID 标识，名为 DEPT_NAME .
(c 1.1)部门由部门编号标识.
(c 1.2)对于每个部门名称，只有一个部门具有该名称；对于每个部门，该部门只有一个名称.
部门{DEPT_ID，DEPT_NAME}-第1页PK {DEPT_ID}-c 1.1AK {DEPT_NAME}-c 1.2 案例1:Emp * ---- 1 部门
[p 2] 由编号 EMP_ID 标识的名为 EMP_NAME 的员工为部门 DEPT_ID 工作./p>
(c 2.1)员工由员工编号标识.
(c 2.2)每个员工只有一个名字；对于每个员工姓名，一个以上的员工可以使用该姓名.
(c 2.3)每个员工都为一个部门工作；每个部门有一个以上雇员可能在该部门工作.
(c 2.4)如果员工在某个部门工作，则该部门必须存在.
员工{EMP_ID，EMP_NAME，DEPT_ID}-第2页PK {EMP_ID}-c 2.1，c 2.2，c 2.3FK {DEPT_ID}参考部门{DEPT_ID}-c 2.4 案例2:Emp * ---- 0..1 部门
[p 2] 存在，由编号 EMP_ID 标识的名为 EMP_NAME 的员工.
(c 2.1)员工由员工编号标识.
(c 2.2)每个员工只有一个名字；对于每个员工姓名，一个以上的员工可以使用该姓名.
[p 3] 由编号 EMP_ID 标识的员工为由编号 DEPT_ID 标识的部门工作.
(c 3.1)每位员工最多只能在一个部门工作；每个部门一名以上员工可以在该部门工作.
(c 3.2)如果员工在部门工作，则该员工必须存在.
(c 3.3)如果员工在某个部门工作，则该部门必须存在.
员工{EMP_ID，EMP_NAME}-第2页PK {EMP_ID}-c 2.1，c 2.2emp_dept {EMP_ID，DEPT_ID}-第3页PK {EMP_ID}-c 3.1FK1 {EMP_ID}参考员工{EMP_ID}-c 3.2FK2 {DEPT_ID}参考部门{DEPT_ID}-c 3.3 案例3:Emp * ---- * 部门
[p 2] 存在，由编号 EMP_ID 标识的名为 EMP_NAME 的员工.
(c 2.1)员工由员工编号标识.
(c 2.2)每个员工只有一个名字；对于每个员工姓名，一个以上的员工可以使用该姓名.
[p 3] 由编号 EMP_ID 标识的员工为由编号 DEPT_ID 标识的部门工作.
(c 3.1)每位员工可以为一个以上部门工作；每个部门一名以上员工可以在该部门工作.
(c 3.2)如果员工在部门工作，则该员工必须存在.
(c 3.3)如果员工在某个部门工作，则该部门必须存在.
员工{EMP_ID，EMP_NAME}-第2页PK {EMP_ID}-c 2.1，c 2.2emp_dept {EMP_ID，DEPT_ID}-第3页PK {EMP_ID，DEPT_ID}-c 3.1FK1 {EMP_ID}参考员工{EMP_ID}-c 3.2FK2 {DEPT_ID}参考部门{DEPT_ID}-c 3.3 案例4:Emp * ---- 1 .. * 部门
这很棘手，通过介绍家庭(主要的，默认的)部门的概念来结合案例1和3.
[p 2] 由编号 EMP_ID 标识的雇员，名为 EMP_NAME ，适用于内政部门 HOME_DEPT_ID .
(c 2.1)员工由员工编号标识.
(c 2.2)每个员工只有一个名字；对于每个员工姓名，一个以上的员工可以使用该姓名.
(c 2.3)每位员工仅为一个家庭部门工作；对于每个家庭部门，一个以上的员工可以为该家庭部门工作.
(c 2.4)如果某个员工在家庭部门工作，则该部门必须存在.
[p 3] 除了内政部门外，以编号 EMP_ID 标识的员工还为另一个以编号 DEPT_ID 标识的部门工作.
(c 3.1)除内政部门外，每位员工都可以为一个以上部门工作；每个部门多个雇员可以在该部门工作.
(c 3.2)如果员工在部门工作，则该员工必须存在.
(c 3.3)如果员工在某个部门工作，则该部门必须存在.
员工{EMP_ID，EMP_NAME，HOME_DEPT_ID}-第2页PK {EMP_ID}-c 2.1，c 2.2，c 2.3FK {HOME_DEPT_ID}参考部门{DEPT_ID}-c 2.4emp_dept {EMP_ID，DEPT_ID}-第3页PK {EMP_ID，DEPT_ID}-c 3.1FK1 {EMP_ID}参考员工{EMP_ID}-c 3.2FK2 {DEPT_ID}参考部门{DEPT_ID}-c 3.3 在应用程序级别上，检查给定雇员的 HOME_DEPT_ID 是否在 emp_dept 中作为 EMP_ID 重复.
Let's say I have a 2 tables like below :
Department:
DeptId Name
Employee:
EmpId Name DeptId
Here I have a 0-to-many relationship between Department and Employee table.
Now when I read the relationship from Department to Employee, it says 1 department can have 0 or more employees.
Does this mean while creating Employee table, I will set DepartmentId (foreign key) as null due to zero or many relationship?
And how do we represent 1 or many relationship like for eg If relationship in ERD says 1 department can have 0 or more employees then DepartmentId (foreign key) will be non nullable in Employee table?
Basically I am just trying to understand how do we give constraint on foreign key based on ERD cardinality (1 to many relation,1 to zero or more relationship).
Can anyone please help me clear this confusion? I will really really appreciate that :)
ERD tools are great, but the most powerful design tool is still natural language, predicates, and constraints. So, when in doubt, use a plain text editor instead of an ERD tool. Also do not allow NULLs in the design phase.
All attributes (columns) NOT NULL
[p x] = predicate x
(c x.y) = constraint x.y
PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key
Starting with the department, this part does not change.
[p 1] Department identified by number DEPT_ID, named DEPT_NAME exists.
(c 1.1) Department is identified by department number.
(c 1.2) For each department name, exactly one department has that name; for each department, that department has exactly one name.
department {DEPT_ID, DEPT_NAME} -- p 1
PK {DEPT_ID} -- c 1.1
AK {DEPT_NAME} -- c 1.2
Case 1: Emp * ---- 1 Dept
[p 2] Employee identified by number EMP_ID, named EMP_NAME, works for department DEPT_ID.
(c 2.1) Employee is identified by employee number.
(c 2.2) Each employee has exactly one name; for each employee name, more than one employee can have that name.
(c 2.3) Each employee works for exactly one department; for each department more than one employee may work for that department.
(c 2.4) If an employee works for a department, then that department must exist.
employee {EMP_ID, EMP_NAME, DEPT_ID} -- p 2
PK {EMP_ID} -- c 2.1, c 2.2, c 2.3
FK {DEPT_ID} REFERENCES department {DEPT_ID} -- c 2.4
Case 2: Emp * ---- 0..1 Dept
[p 2] Employee identified by number EMP_ID, named EMP_NAME, exists.
(c 2.1) Employee is identified by employee number.
(c 2.2) Each employee has exactly one name; for each employee name, more than one employee can have that name.
[p 3] Employee identified by number EMP_ID works for department identified by number DEPT_ID.
(c 3.1) Each employee works for at most one department; for each department more than one employee may work in that department.
(c 3.2) If an employee works for a department, then that employee must exist.
(c 3.3) If an employee works for a department, then that department must exist.
employee {EMP_ID, EMP_NAME} -- p 2
PK {EMP_ID} -- c 2.1, c 2.2
emp_dept {EMP_ID, DEPT_ID} -- p 3
PK {EMP_ID} -- c 3.1
FK1 {EMP_ID} REFERENCES employee {EMP_ID} -- c 3.2
FK2 {DEPT_ID} REFERENCES department {DEPT_ID} -- c 3.3
Case 3: Emp * ---- * Dept
[p 2] Employee identified by number EMP_ID, named EMP_NAME, exists.
(c 2.1) Employee is identified by employee number.
(c 2.2) Each employee has exactly one name; for each employee name, more than one employee can have that name.
[p 3] Employee identified by number EMP_ID works for department identified by number DEPT_ID.
(c 3.1) Each employee may work for more than one department; for each department more than one employee may work in that department.
(c 3.2) If an employee works for a department, then that employee must exist.
(c 3.3) If an employee works for a department, then that department must exist.
employee {EMP_ID, EMP_NAME} -- p 2
PK {EMP_ID} -- c 2.1, c 2.2
emp_dept {EMP_ID, DEPT_ID} -- p 3
PK {EMP_ID, DEPT_ID} -- c 3.1
FK1 {EMP_ID} REFERENCES employee {EMP_ID} -- c 3.2
FK2 {DEPT_ID} REFERENCES department {DEPT_ID} -- c 3.3
Case 4: Emp * ---- 1..* Dept
This one is tricky, combine cases 1 and 3 by introducing the concept of a home (primary, default) department.
[p 2] Employee identified by number EMP_ID, named EMP_NAME, works for home department HOME_DEPT_ID.
(c 2.1) Employee is identified by employee number.
(c 2.2) Each employee has exactly one name; for each employee name, more than one employee can have that name.
(c 2.3) Each employee works for exactly one home department; for each home department more than one employee may work for that home department.
(c 2.4) If an employee works for a home department, then that department must exist.
[p 3] In addition to the home department, employee identified by number EMP_ID also works for another department identified by number DEPT_ID.
(c 3.1) In addition to the home department, each employee may work for more than one other department; for each department more than one employee may work for that department.
(c 3.2) If an employee works for a department, then that employee must exist.
(c 3.3) If an employee works for a department, then that department must exist.
employee {EMP_ID, EMP_NAME, HOME_DEPT_ID} -- p 2
PK {EMP_ID} -- c 2.1, c 2.2, c 2.3
FK {HOME_DEPT_ID} REFERENCES department {DEPT_ID} -- c 2.4
emp_dept {EMP_ID, DEPT_ID} -- p 3
PK {EMP_ID, DEPT_ID} -- c 3.1
FK1 {EMP_ID} REFERENCES employee {EMP_ID} -- c 3.2
FK2 {DEPT_ID} REFERENCES department {DEPT_ID} -- c 3.3
On the application level check that, for a given employee, HOME_DEPT_ID is not repeated as EMP_ID in the emp_dept.
这篇关于我们如何基于ERD基数对外键列施加约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
