如何在MySQL中将复杂的约束应用于数据库表? [英] How to apply complex constraints to a database table in MySQL?

问题描述

由于我正处于为我的一个项目建立数据库的最后阶段，因此我想到了一个额外的约束，需要将其添加到任务"表中(请参见下图)，但是我不确定如何可以在MySQL中实现.

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.

原始数据库架构(无标记): 单击此处.

带有标记的数据库架构:

对于每个作业(job)，分配了WBS代码列表(wbscodelist).这些列表中的每一个都包含许多适用于该作业的WBS代码(wbscodeitem).一个例子是:

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:

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.

此刻，task.fk_wbsCodeItemID是wbscodeitem.wbsCodeItemID的外键(以橙色标记).

At the moment, task.fk_wbsCodeItemID is a foreign key of wbscodeitem.wbsCodeItemID (marked up in orange).

我在这里面临的问题是task可能会使用不适用于该job的WBS代码.

The problem that I am facing here is that a task could potentially use a WBS Code that does not apply to that job.

我想对task.fk_wbsCodeItemID包括一个进一步的约束，以便它可以采用的值取决于该工作的wbscodeitem.fk_wbsCodeListID和job.wbscodeitem.fk_wbsCodeListID相等(用红色标记).

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).

如何在此数据库模式的MySQL中包含此约束?这个问题可能是由于该数据库的当前设计(我需要更改它)吗?

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)?

我了解这可能需要更多详细信息，因此我可以提供更多详细信息或在必要时进行澄清.

I understand this may require a little more detail, so I can include further details or clarify if necessary.

推荐答案

一种方法是通过受控冗余.您可以将功能依赖项jobNumber -> fk_wbsCodeListID归一化为joblocation和task，并使用复合FK约束来防止不一致.类似地，功能依赖项wbsCodeItemID -> fk_wbsCodeListID可以反规范化为task.然后，task中重叠的复合FK约束将强制执行您的要求:

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:

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;

请注意组合索引以匹配组合FK约束.

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:

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 ;

这篇关于如何在MySQL中将复杂的约束应用于数据库表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！