As I am in the final stages of setting up a database for one of my projects, I have thought of an additional constraint that would need to be added to the Task table (see image below), but I am not sure how this can be implemented in MySQL.
由于我正在为我的一个项目建立一个数据库的最后阶段，我想到了一个需要添加到任务表的额外约束(参见下图)，但是我不确定如何在MySQL中实现这个约束。
Original Database Schema (without markups): Click here.
原始数据库模式(没有标记):点击这里。
Database Schema with Markups: 
数据库模式与标记:
For each job (job), a WBS Code List (wbscodelist) is assigned. Each of these lists contain a number of WBS Codes (wbscodeitem) that apply to that job. An example would be:
对于每个作业(作业)，都分配一个WBS代码列表(wbscodelist)。每个列表都包含许多适用于该作业的WBS代码(wbscodeitem)。一个例子是:
Job A uses WBS Code List #1
Job B uses WBS Code List #2
Job C uses WBS Code List #1
etc.
WBS Code List #1 has codes: [100, 105, 110, 115, 120]
WBS Code List #2 has codes: [2180, 2190]
etc.
At the moment, task.fk_wbsCodeItemID is a foreign key of wbscodeitem.wbsCodeItemID (marked up in orange).
目前,任务。fk_wbsCodeItemID是wbscodeitem的外键。wbsCodeItemID(用橙色标记)。
The problem that I am facing here is that a task could potentially use a WBS Code that does not apply to that job.
我在这里面临的问题是，一个任务可能使用一个不适用于该任务的WBS代码。
I would like to include a further constraint to task.fk_wbsCodeItemID so that the values it can take are dependent on wbscodeitem.fk_wbsCodeListID and job.wbscodeitem.fk_wbsCodeListID being equal for that job (marked up in red).
我想对task加上一个进一步的约束。fk_wbsCodeItemID，以便它可以接受的值依赖于wbscodeitem。fk_wbsCodeListID job.wbscodeitem。fk_wbsCodeListID对于该作业是相等的(用红色标记)。
How can I include this constraint within MySQL for this database schema? Would this issue possibly be due to the current design of this database (and would I need to change it)?
如何在MySQL中为这个数据库模式包含这个约束?这个问题可能是由于这个数据库的当前设计(我是否需要更改它)吗?
I understand this may require a little more detail, so I can include further details or clarify if necessary.
我知道这可能需要更多的细节，所以我可以包括更多的细节，或者在必要的时候澄清。
4 个解决方案
#1
2
One way to do it is via controlled redundancy. You can denormalize the functional dependencies jobNumber -> fk_wbsCodeListID into joblocation and task, and use composite FK constraints to prevent inconsistencies. Similarly, the functional dependency wbsCodeItemID -> fk_wbsCodeListID can be denormalized into task. The overlapping composite FK constraints in task will then enforce your requirement:
一种方法是通过控制冗余实现。您可以将函数依赖项jobNumber -> fk_wbsCodeListID非规范化为joblocation和task，并使用复合FK约束来防止不一致性。类似地，函数依赖项wbsCodeItemID -> fk_wbsCodeListID可以反规范化为task。任务中的重叠复合FK约束将执行您的需求:
CREATE TABLE `wbscodelist` (
`wbsCodeListID` int(11) NOT NULL,
`description` varchar(45) NOT NULL,
PRIMARY KEY (`wbsCodeListID`)
) ENGINE=InnoDB;
CREATE TABLE `wbscodeitem` (
`wbsCodeItemID` int(11) NOT NULL,
`wbsCode` varchar(10) NOT NULL,
`description` varchar(50) NOT NULL,
`notes` varchar(80) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
PRIMARY KEY (`wbsCodeItemID`),
UNIQUE KEY (`wbsCodeItemID`,`fk_wbsCodeListID`),
KEY (`fk_wbsCodeListID`),
FOREIGN KEY (`fk_wbsCodeListID`) REFERENCES `wbscodelist` (`wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE `job` (
`jobNumber` varchar(15) NOT NULL,
`jobName` varchar(45) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
`isActive` bit(1) NOT NULL,
PRIMARY KEY (`jobNumber`),
UNIQUE KEY (`jobNumber`,`fk_wbsCodeListID`),
KEY (`fk_wbsCodeListID`),
FOREIGN KEY (`fk_wbsCodeListID`) REFERENCES `wbscodelist` (`wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE `joblocation` (
`jobLocationID` int(11) NOT NULL,
`roomNumber` varchar(25) NOT NULL,
`fk_jobNumber` varchar(15) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
PRIMARY KEY (`jobLocationID`),
KEY (`fk_jobNumber`,`fk_wbsCodeListID`),
KEY (`jobLocationID`,`fk_wbsCodeListID`),
FOREIGN KEY (`fk_jobNumber`, `fk_wbsCodeListID`) REFERENCES `job` (`jobNumber`, `fk_wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE `task` (
`taskID` int(11) NOT NULL,
`fk_JobLocationID` int(11) NOT NULL,
`fk_JobNumber` varchar(15) NOT NULL,
`fk_wbsCodeItemID` int(11) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
PRIMARY KEY (`taskID`),
KEY (`fk_wbsCodeItemID`,`fk_wbsCodeListID`),
KEY (`fk_JobLocationID`,`fk_wbsCodeListID`),
FOREIGN KEY (`fk_JobLocationID`, `fk_wbsCodeListID`) REFERENCES `joblocation` (`jobLocationID`, `fk_wbsCodeListID`) ON UPDATE CASCADE,
FOREIGN KEY (`fk_wbsCodeItemID`, `fk_wbsCodeListID`) REFERENCES `wbscodeitem` (`wbsCodeItemID`, `fk_wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
Note the composite indexes to match the composite FK constraints.
注意要匹配复合FK约束的复合索引。
An alternative option is to create triggers to check that the inserted/updated FK values are related via joins:
另一种选择是创建触发器，通过连接检查插入/更新的FK值是否相关:
DELIMITER ;;
CREATE TRIGGER check_task_insert BEFORE INSERT ON task
FOR EACH ROW
BEGIN
IF NOT EXISTS (
SELECT 1
FROM joblocation loc
JOIN job ON loc.fk_jobNumber = job.jobNumber
JOIN wbscodeitem itm ON job.fk_wbsCodeListID = itm.fk_wbsCodeListID
WHERE loc.jobLocationID = new.fk_jobLocationID
AND itm.wbsCodeItemID = new.fk_wbsCodeItemID
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'fk_wbsCodeItemID doesn\'t match fk_wbsCodeListID of associated job';
END IF;
END;
;;
CREATE TRIGGER check_task_update BEFORE UPDATE ON task
FOR EACH ROW
BEGIN
IF NOT EXISTS (
SELECT 1
FROM joblocation loc
JOIN job ON loc.fk_jobNumber = job.jobNumber
JOIN wbscodeitem itm ON job.fk_wbsCodeListID = itm.fk_wbsCodeListID
WHERE loc.jobLocationID = new.fk_jobLocationID
AND itm.wbsCodeItemID = new.fk_wbsCodeItemID
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'fk_wbsCodeItemID doesn\'t match fk_wbsCodeListID of associated job';
END IF;
END;
;;
DELIMITER ;
#2
0
Can you explicitly constrain tasks to reference only code items applicable to their respective job? Yes. The question is more whether you need to do that at the database level, and whether the added complexity to your schema is worth it.
是否可以显式地约束任务，使其仅引用适用于其各自作业的代码项?是的。问题是您是否需要在数据库级别上执行这些操作，以及您的模式的复杂性是否值得。
Which codes are available to which tasks is practically a textbook example of a business logic concern, and while it's not unheard of to incorporate business logic into the schema like you're suggesting, it's getting close to the boundary of what relational databases are best at. The database provides structure, and can make basic guarantees as to its consistency by enforcing direct relationships; what you have is an indirect relationship (task to code items associated with job), which you will need to either make direct or enforce by validating your inputs to the database.
可以使用哪些代码来实现业务逻辑关注的典型范例，尽管在您所建议的模式中加入业务逻辑并不是没有听说过，但是它已经接近关系数据库最擅长的边界了。数据库提供了结构，并可以通过加强直接关系对其一致性作出基本保证;您拥有的是一种间接关系(任务到与作业相关的代码项)，您需要通过验证数据库的输入来直接或强制使用它。
To enforce the constraint in your schema, you'll need a new table wbscodelist_items which acts as a junction table between wbscodelist and wbscodeitem, replacing the one:many relationship defined by wbscodeitem.fk_wbsCodeListID with a many:many relationship. This opens its own can of worms -- now you can have code items on multiple lists, which may not be what you want! But task can reference wbscodelist_items which ensures that it cannot have a code item which is not on the correct list.
要在模式中强制约束，您需要一个新的表wbscodelist_items，它充当wbscodelist和wbscodeitem之间的连接表，替换wbscodeitem定义的一个:many关系。fk_wbsCodeListID与许多:许多的关系。这就打开了自己的蠕虫罐头——现在您可以在多个列表中拥有代码项，这可能不是您想要的!但是task可以引用wbscodelist_items，以确保它不能有不在正确列表中的代码项。
Even with all that, there's still no guarantee that the list belongs to the task's job. Simply giving wbscodelist_items a foreign key to job won't cut it -- you'll wind up with two duplicate relationships (tasks to jobs via joblocation and wbscodelist_items, and code items to jobs via wbscodelist_items and wbscodelist) which are completely unchecked and so could be inconsistent. In order to enforce the relationship without giving yourself even worse structural problems, you'd have to roll wbscodelist into wbscodelist_items and joblocation into task, duplicating data and generally making life more difficult anyway.
尽管如此，仍然不能保证列表属于任务的任务。简单地给wbscodelist_items一个外键并不会减少它——您最终会得到两个重复的关系(通过joblocation和wbscodelist_items对作业执行任务，通过wbscodelist_items和wbscodelist对作业执行代码)，它们是完全不检查的，因此可能是不一致的。为了在不给自己带来更糟糕的结构问题的情况下加强这种关系，您必须将wbscodelist_items和joblocation转换为任务、复制数据并通常使生活变得更困难。
All told, I'd keep your structure and just validate your inputs.
总之，我将保留您的结构并验证您的输入。
#3
0
I have focused only on key attributes, and have in total six relations (tables). All constraints are solved by PK and FK, no need for triggers. Admittedly I did not quite understand the concept of code_list, if you need a list to easily manage sets of codes, than you may add another relation for that.
我只关注关键属性，总共有6个关系(表)。所有的约束都由PK和FK解决，不需要触发器。无可否认，我不太理解code_list的概念，如果您需要一个列表来轻松地管理代码集，那么您可以为此添加另一个关系。
Note:
注意:
[Px] = predicate x [cx.y] = constraint x.y PK = PRIMARY KEY AKn = ALTERNATE KEY (UNIQUE) FKn = FOREIGN KEY
[P1] Job (job_ID) exists.
(P1)工作(job_ID)存在。
[c1.1] Job is identifed by job_ID.
作业由job_ID标识。
job {job_ID} -- P1
PK {job_ID} -- c1.1
[P2] Location (loc_ID) exists.
(P2)的位置(loc_ID)存在。
[c2.1] Location is identifed by loc_ID.
[c2.1]位置由loc_ID标识。
location {loc_ID} -- P2
PK {loc_ID} -- c2.1
[P3] Job (job_ID) is assigned to location (loc_ID).
作业(job_ID)被分配到location (loc_ID)。
[c3.1] Each job may be assigned to more than one location; for each location that location may have more than one assigned job.
[c3.1]每项工作可分配到多个地点;对于每个位置可能有多个指定的工作。
[c3.2] If a job is assigned to a location then that job must exist.
[c3.2]如果一个工作被分配到一个位置，那么这个工作就必须存在。
[c3.3] If a job is assigned to a location then that location must exist.
如果一个作业被分配到一个位置，那么这个位置必须存在。
job_location {job_ID, loc_ID} -- P3
PK {job_ID, loc_ID} -- c3.1
FK1 {job_ID} REFERENCES job {job_ID} -- c3.2
FK2 {loc_ID} REFERENCES location {loc_ID} -- c3.3
[P4] WBS code (wbs_ID) exists.
WBS码(wbs_ID)存在。
[c4.1] WBS code is identifed by wbs_ID.
WBS代码由wbs_ID标识。
wbs_code {wbs_ID} -- P4
PK {wbs_ID} -- c4.1
[P5] Job (job_ID) is assigned wbs code (wbs_ID).
[P5] Job (job_ID)被分配wbs代码(wbs_ID)。
[c5.1] Each job may be assigned more than one wbs code; for each wbs code that wbs code may be assigend to more than one job.
[c5.1]每个作业可能被分配多个wbs代码;对于每个wbs代码来说，wbs代码可能是一个以上的任务。
[c5.2] If a job is assigned a wbs code, then that job must exist.
如果一个作业被分配了wbs代码，那么这个作业必须存在。
[c5.3] If a job is assigned a wbs code, then that wbs code must exist.
如果一个作业被分配了一个wbs代码，那么这个wbs代码必须存在。
job_wbs {job_ID, wbs_ID} -- P5
PK {job_ID, wbs_ID} -- c5.1
FK1 {job_ID} REFERENCES job {job_ID} -- c5.2
FK2 {wbs_ID} REFERENCES wbs_code {wbs_ID} -- c5.3
[P6] Taks number (job_task_No) of Job (job_ID) is performed at location (loc_ID), with assigned wbs code (wbs_ID).
[P6] Taks number (job_task_No)是在location (loc_ID)上执行的，带有指定的wbs代码(wbs_ID)。
[c6.1] Task is identified by combination of job_ID and job_task_No.
[c6.1]任务通过job_ID和job_task_No的组合进行标识。
[c6.2] If a task of a job is performed, then that job must exist.
[c6.2]如果一个任务被执行，那么这个任务必须存在。
[c6.3] If a task of a job is performed at a location, then that job must be assigend to that location.
[c6.3]如果一个任务在某个位置执行，那么该任务必须被分配到该位置。
[c6.4] If a task of a job is performed with assigend wbs code, then that wbs code must be assigned to that job.
[c6.4]如果一个作业的任务是用分配的wbs代码执行的，那么必须将wbs代码分配给该作业。
task {job_ID, job_task_No, loc_ID, wbs_ID} -- P6
PK {job_ID, job_task_No} -- c6.1
FK1 {job_ID} REFERENCES job {job_ID} -- c6.2
FK2 {job_ID, loc_ID} REFERENCES job_location {job_ID, loc_ID} -- c6.3
FK3 {job_ID, wbs_ID} REFERENCES job_wbs {job_ID, wbs_ID} -- c6.4
#4
0
The solution is fairly simple. It just takes a little tweaking with the design.
解决方法相当简单。这只需要对设计稍加修改。
It looks like a job location can host only one job but a job could be spread out over several locations. Also a list may contain several code items and an item can appear on only one list with each list associated with only one job.
看起来一个作业位置只能承载一个作业，但是一个作业可以分布在多个位置。此外，一个列表可能包含多个代码项，并且一个项只能出现在一个列表中，每个列表只与一个作业关联。
Let's work with that for a moment.
让我们一起来解决这个问题。
create table Jobs(
ID int primary key,
Name vachar( 45 ),
IsActive bit
);
create table Locations(
ID int primary key,
RoomNum varchar( 25 )
);
create table JobLocations(
LocID int references Locations( ID ),
JobID int unique references Jobs( ID ),
constraint PK_JobLocations primary key( LocID, JobID )
);
create table Items(
ID int primary key,
Code varchar( 10 ),
Description varchar( 50 ),
Notes varchar( 80 )
);
create table ItemLists(
ID int primary key,
Description varchar( 45 )
);
create table ListItems(
ListID int references ItemList( ID ),
ItemID int unique references Items( ID ),
constraint PK_ListItems primary key( ListID, ItemID )
);
Now each list must be associated with a job.
现在，每个列表都必须与作业相关联。
create table JobLists(
JobId int references Jobs( ID ),
ListID int references ItemLists( ID ),
constraint PK_JobLists primary key( JobID, listID )
);
Now you have a task which associates with one item for a particular job at a particular location. In order to have complete data integrity, the task must refer to a job, its location, a list associated with the job and then an item which appears on the list. This means you have to add two fields to the Tasks table.
现在，您有一个任务，该任务与特定位置上的特定作业的一个项目关联。为了拥有完整的数据完整性，任务必须引用一个作业、它的位置、一个与作业相关的列表，然后引用一个出现在列表中的项。这意味着您必须向Tasks表添加两个字段。
create table Tasks(
ID int primary key,
LocID int,
JobID int,
ListID int,
ItemID int,
constraint FK_TaskJobLoc( LocID, JobID ) references JobLocations( LocID, JobID ),
constraint FK_TaskJobList( JobID, ListID ) references JobLists( JobID, ListID ),
constraint FK_TaskListItem( ListID, ItemID ) references ListItems( ListID, ItemID )
);
Now you've guaranteed that the list must be associated with a job at a specific location and the item must be associated with the list. Thus the item must be associated with the job.
现在您已经保证列表必须与特定位置的作业相关联，并且项目必须与列表相关联。因此，项目必须与作业相关联。
I've thrown this together rather quickly so check the complete chain of references carefully. However, you can see that if you want to refer to an object at the head of a reference chain and an object at the end of that chain, you have to use the intermediate references.
我已经很快地将它们组合在一起，所以请仔细检查完整的引用链。但是，您可以看到，如果您希望引用位于引用链头部的对象和位于该链末尾的对象，则必须使用中间引用。
#1
2
One way to do it is via controlled redundancy. You can denormalize the functional dependencies jobNumber -> fk_wbsCodeListID into joblocation and task, and use composite FK constraints to prevent inconsistencies. Similarly, the functional dependency wbsCodeItemID -> fk_wbsCodeListID can be denormalized into task. The overlapping composite FK constraints in task will then enforce your requirement:
一种方法是通过控制冗余实现。您可以将函数依赖项jobNumber -> fk_wbsCodeListID非规范化为joblocation和task，并使用复合FK约束来防止不一致性。类似地，函数依赖项wbsCodeItemID -> fk_wbsCodeListID可以反规范化为task。任务中的重叠复合FK约束将执行您的需求:
CREATE TABLE `wbscodelist` (
`wbsCodeListID` int(11) NOT NULL,
`description` varchar(45) NOT NULL,
PRIMARY KEY (`wbsCodeListID`)
) ENGINE=InnoDB;
CREATE TABLE `wbscodeitem` (
`wbsCodeItemID` int(11) NOT NULL,
`wbsCode` varchar(10) NOT NULL,
`description` varchar(50) NOT NULL,
`notes` varchar(80) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
PRIMARY KEY (`wbsCodeItemID`),
UNIQUE KEY (`wbsCodeItemID`,`fk_wbsCodeListID`),
KEY (`fk_wbsCodeListID`),
FOREIGN KEY (`fk_wbsCodeListID`) REFERENCES `wbscodelist` (`wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE `job` (
`jobNumber` varchar(15) NOT NULL,
`jobName` varchar(45) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
`isActive` bit(1) NOT NULL,
PRIMARY KEY (`jobNumber`),
UNIQUE KEY (`jobNumber`,`fk_wbsCodeListID`),
KEY (`fk_wbsCodeListID`),
FOREIGN KEY (`fk_wbsCodeListID`) REFERENCES `wbscodelist` (`wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE `joblocation` (
`jobLocationID` int(11) NOT NULL,
`roomNumber` varchar(25) NOT NULL,
`fk_jobNumber` varchar(15) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
PRIMARY KEY (`jobLocationID`),
KEY (`fk_jobNumber`,`fk_wbsCodeListID`),
KEY (`jobLocationID`,`fk_wbsCodeListID`),
FOREIGN KEY (`fk_jobNumber`, `fk_wbsCodeListID`) REFERENCES `job` (`jobNumber`, `fk_wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE `task` (
`taskID` int(11) NOT NULL,
`fk_JobLocationID` int(11) NOT NULL,
`fk_JobNumber` varchar(15) NOT NULL,
`fk_wbsCodeItemID` int(11) NOT NULL,
`fk_wbsCodeListID` int(11) NOT NULL,
PRIMARY KEY (`taskID`),
KEY (`fk_wbsCodeItemID`,`fk_wbsCodeListID`),
KEY (`fk_JobLocationID`,`fk_wbsCodeListID`),
FOREIGN KEY (`fk_JobLocationID`, `fk_wbsCodeListID`) REFERENCES `joblocation` (`jobLocationID`, `fk_wbsCodeListID`) ON UPDATE CASCADE,
FOREIGN KEY (`fk_wbsCodeItemID`, `fk_wbsCodeListID`) REFERENCES `wbscodeitem` (`wbsCodeItemID`, `fk_wbsCodeListID`) ON UPDATE CASCADE
) ENGINE=InnoDB;
Note the composite indexes to match the composite FK constraints.
注意要匹配复合FK约束的复合索引。
An alternative option is to create triggers to check that the inserted/updated FK values are related via joins:
另一种选择是创建触发器，通过连接检查插入/更新的FK值是否相关:
DELIMITER ;;
CREATE TRIGGER check_task_insert BEFORE INSERT ON task
FOR EACH ROW
BEGIN
IF NOT EXISTS (
SELECT 1
FROM joblocation loc
JOIN job ON loc.fk_jobNumber = job.jobNumber
JOIN wbscodeitem itm ON job.fk_wbsCodeListID = itm.fk_wbsCodeListID
WHERE loc.jobLocationID = new.fk_jobLocationID
AND itm.wbsCodeItemID = new.fk_wbsCodeItemID
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'fk_wbsCodeItemID doesn\'t match fk_wbsCodeListID of associated job';
END IF;
END;
;;
CREATE TRIGGER check_task_update BEFORE UPDATE ON task
FOR EACH ROW
BEGIN
IF NOT EXISTS (
SELECT 1
FROM joblocation loc
JOIN job ON loc.fk_jobNumber = job.jobNumber
JOIN wbscodeitem itm ON job.fk_wbsCodeListID = itm.fk_wbsCodeListID
WHERE loc.jobLocationID = new.fk_jobLocationID
AND itm.wbsCodeItemID = new.fk_wbsCodeItemID
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'fk_wbsCodeItemID doesn\'t match fk_wbsCodeListID of associated job';
END IF;
END;
;;
DELIMITER ;
#2
0
Can you explicitly constrain tasks to reference only code items applicable to their respective job? Yes. The question is more whether you need to do that at the database level, and whether the added complexity to your schema is worth it.
是否可以显式地约束任务，使其仅引用适用于其各自作业的代码项?是的。问题是您是否需要在数据库级别上执行这些操作，以及您的模式的复杂性是否值得。
Which codes are available to which tasks is practically a textbook example of a business logic concern, and while it's not unheard of to incorporate business logic into the schema like you're suggesting, it's getting close to the boundary of what relational databases are best at. The database provides structure, and can make basic guarantees as to its consistency by enforcing direct relationships; what you have is an indirect relationship (task to code items associated with job), which you will need to either make direct or enforce by validating your inputs to the database.
可以使用哪些代码来实现业务逻辑关注的典型范例，尽管在您所建议的模式中加入业务逻辑并不是没有听说过，但是它已经接近关系数据库最擅长的边界了。数据库提供了结构，并可以通过加强直接关系对其一致性作出基本保证;您拥有的是一种间接关系(任务到与作业相关的代码项)，您需要通过验证数据库的输入来直接或强制使用它。
To enforce the constraint in your schema, you'll need a new table wbscodelist_items which acts as a junction table between wbscodelist and wbscodeitem, replacing the one:many relationship defined by wbscodeitem.fk_wbsCodeListID with a many:many relationship. This opens its own can of worms -- now you can have code items on multiple lists, which may not be what you want! But task can reference wbscodelist_items which ensures that it cannot have a code item which is not on the correct list.
要在模式中强制约束，您需要一个新的表wbscodelist_items，它充当wbscodelist和wbscodeitem之间的连接表，替换wbscodeitem定义的一个:many关系。fk_wbsCodeListID与许多:许多的关系。这就打开了自己的蠕虫罐头——现在您可以在多个列表中拥有代码项，这可能不是您想要的!但是task可以引用wbscodelist_items，以确保它不能有不在正确列表中的代码项。
Even with all that, there's still no guarantee that the list belongs to the task's job. Simply giving wbscodelist_items a foreign key to job won't cut it -- you'll wind up with two duplicate relationships (tasks to jobs via joblocation and wbscodelist_items, and code items to jobs via wbscodelist_items and wbscodelist) which are completely unchecked and so could be inconsistent. In order to enforce the relationship without giving yourself even worse structural problems, you'd have to roll wbscodelist into wbscodelist_items and joblocation into task, duplicating data and generally making life more difficult anyway.
尽管如此，仍然不能保证列表属于任务的任务。简单地给wbscodelist_items一个外键并不会减少它——您最终会得到两个重复的关系(通过joblocation和wbscodelist_items对作业执行任务，通过wbscodelist_items和wbscodelist对作业执行代码)，它们是完全不检查的，因此可能是不一致的。为了在不给自己带来更糟糕的结构问题的情况下加强这种关系，您必须将wbscodelist_items和joblocation转换为任务、复制数据并通常使生活变得更困难。
All told, I'd keep your structure and just validate your inputs.
总之，我将保留您的结构并验证您的输入。
#3
0
I have focused only on key attributes, and have in total six relations (tables). All constraints are solved by PK and FK, no need for triggers. Admittedly I did not quite understand the concept of code_list, if you need a list to easily manage sets of codes, than you may add another relation for that.
我只关注关键属性，总共有6个关系(表)。所有的约束都由PK和FK解决，不需要触发器。无可否认，我不太理解code_list的概念，如果您需要一个列表来轻松地管理代码集，那么您可以为此添加另一个关系。
Note:
注意:
[Px] = predicate x [cx.y] = constraint x.y PK = PRIMARY KEY AKn = ALTERNATE KEY (UNIQUE) FKn = FOREIGN KEY
[P1] Job (job_ID) exists.
(P1)工作(job_ID)存在。
[c1.1] Job is identifed by job_ID.
作业由job_ID标识。
job {job_ID} -- P1
PK {job_ID} -- c1.1
[P2] Location (loc_ID) exists.
(P2)的位置(loc_ID)存在。
[c2.1] Location is identifed by loc_ID.
[c2.1]位置由loc_ID标识。
location {loc_ID} -- P2
PK {loc_ID} -- c2.1
[P3] Job (job_ID) is assigned to location (loc_ID).
作业(job_ID)被分配到location (loc_ID)。
[c3.1] Each job may be assigned to more than one location; for each location that location may have more than one assigned job.
[c3.1]每项工作可分配到多个地点;对于每个位置可能有多个指定的工作。
[c3.2] If a job is assigned to a location then that job must exist.
[c3.2]如果一个工作被分配到一个位置，那么这个工作就必须存在。
[c3.3] If a job is assigned to a location then that location must exist.
如果一个作业被分配到一个位置，那么这个位置必须存在。
job_location {job_ID, loc_ID} -- P3
PK {job_ID, loc_ID} -- c3.1
FK1 {job_ID} REFERENCES job {job_ID} -- c3.2
FK2 {loc_ID} REFERENCES location {loc_ID} -- c3.3
[P4] WBS code (wbs_ID) exists.
WBS码(wbs_ID)存在。
[c4.1] WBS code is identifed by wbs_ID.
WBS代码由wbs_ID标识。
wbs_code {wbs_ID} -- P4
PK {wbs_ID} -- c4.1
[P5] Job (job_ID) is assigned wbs code (wbs_ID).
[P5] Job (job_ID)被分配wbs代码(wbs_ID)。
[c5.1] Each job may be assigned more than one wbs code; for each wbs code that wbs code may be assigend to more than one job.
[c5.1]每个作业可能被分配多个wbs代码;对于每个wbs代码来说，wbs代码可能是一个以上的任务。
[c5.2] If a job is assigned a wbs code, then that job must exist.
如果一个作业被分配了wbs代码，那么这个作业必须存在。
[c5.3] If a job is assigned a wbs code, then that wbs code must exist.
如果一个作业被分配了一个wbs代码，那么这个wbs代码必须存在。
job_wbs {job_ID, wbs_ID} -- P5
PK {job_ID, wbs_ID} -- c5.1
FK1 {job_ID} REFERENCES job {job_ID} -- c5.2
FK2 {wbs_ID} REFERENCES wbs_code {wbs_ID} -- c5.3
[P6] Taks number (job_task_No) of Job (job_ID) is performed at location (loc_ID), with assigned wbs code (wbs_ID).
[P6] Taks number (job_task_No)是在location (loc_ID)上执行的，带有指定的wbs代码(wbs_ID)。
[c6.1] Task is identified by combination of job_ID and job_task_No.
[c6.1]任务通过job_ID和job_task_No的组合进行标识。
[c6.2] If a task of a job is performed, then that job must exist.
[c6.2]如果一个任务被执行，那么这个任务必须存在。
[c6.3] If a task of a job is performed at a location, then that job must be assigend to that location.
[c6.3]如果一个任务在某个位置执行，那么该任务必须被分配到该位置。
[c6.4] If a task of a job is performed with assigend wbs code, then that wbs code must be assigned to that job.
[c6.4]如果一个作业的任务是用分配的wbs代码执行的，那么必须将wbs代码分配给该作业。
task {job_ID, job_task_No, loc_ID, wbs_ID} -- P6
PK {job_ID, job_task_No} -- c6.1
FK1 {job_ID} REFERENCES job {job_ID} -- c6.2
FK2 {job_ID, loc_ID} REFERENCES job_location {job_ID, loc_ID} -- c6.3
FK3 {job_ID, wbs_ID} REFERENCES job_wbs {job_ID, wbs_ID} -- c6.4
#4
0
The solution is fairly simple. It just takes a little tweaking with the design.
解决方法相当简单。这只需要对设计稍加修改。
It looks like a job location can host only one job but a job could be spread out over several locations. Also a list may contain several code items and an item can appear on only one list with each list associated with only one job.
看起来一个作业位置只能承载一个作业，但是一个作业可以分布在多个位置。此外，一个列表可能包含多个代码项，并且一个项只能出现在一个列表中，每个列表只与一个作业关联。
Let's work with that for a moment.
让我们一起来解决这个问题。
create table Jobs(
ID int primary key,
Name vachar( 45 ),
IsActive bit
);
create table Locations(
ID int primary key,
RoomNum varchar( 25 )
);
create table JobLocations(
LocID int references Locations( ID ),
JobID int unique references Jobs( ID ),
constraint PK_JobLocations primary key( LocID, JobID )
);
create table Items(
ID int primary key,
Code varchar( 10 ),
Description varchar( 50 ),
Notes varchar( 80 )
);
create table ItemLists(
ID int primary key,
Description varchar( 45 )
);
create table ListItems(
ListID int references ItemList( ID ),
ItemID int unique references Items( ID ),
constraint PK_ListItems primary key( ListID, ItemID )
);
Now each list must be associated with a job.
现在，每个列表都必须与作业相关联。
create table JobLists(
JobId int references Jobs( ID ),
ListID int references ItemLists( ID ),
constraint PK_JobLists primary key( JobID, listID )
);
Now you have a task which associates with one item for a particular job at a particular location. In order to have complete data integrity, the task must refer to a job, its location, a list associated with the job and then an item which appears on the list. This means you have to add two fields to the Tasks table.
现在，您有一个任务，该任务与特定位置上的特定作业的一个项目关联。为了拥有完整的数据完整性，任务必须引用一个作业、它的位置、一个与作业相关的列表，然后引用一个出现在列表中的项。这意味着您必须向Tasks表添加两个字段。
create table Tasks(
ID int primary key,
LocID int,
JobID int,
ListID int,
ItemID int,
constraint FK_TaskJobLoc( LocID, JobID ) references JobLocations( LocID, JobID ),
constraint FK_TaskJobList( JobID, ListID ) references JobLists( JobID, ListID ),
constraint FK_TaskListItem( ListID, ItemID ) references ListItems( ListID, ItemID )
);
Now you've guaranteed that the list must be associated with a job at a specific location and the item must be associated with the list. Thus the item must be associated with the job.
现在您已经保证列表必须与特定位置的作业相关联，并且项目必须与列表相关联。因此，项目必须与作业相关联。
I've thrown this together rather quickly so check the complete chain of references carefully. However, you can see that if you want to refer to an object at the head of a reference chain and an object at the end of that chain, you have to use the intermediate references.
我已经很快地将它们组合在一起，所以请仔细检查完整的引用链。但是，您可以看到，如果您希望引用位于引用链头部的对象和位于该链末尾的对象，则必须使用中间引用。