mysql - 错误 1215 : database design with foreign key

Question

我正在尝试创建三个表，例如同事、经理和出勤表。出勤表应包含其他两个表中的员工和经理详细信息，以便标记出勤情况。我创建了这个 SQL 脚本。我不确定我在哪里犯了错误。

CREATE TABLE associate (
    id      INT             NOT NULL,
    idmanager INT           NOT NULL,
    emp_id  DATE  NOT NULL,
    emp_name VARCHAR(25)  NOT NULL,
    FOREIGN KEY (id)  REFERENCES attendance (associate_id) ON DELETE CASCADE,
    FOREIGN KEY (idmanager)  REFERENCES attendance (manager_idmanager)    ON DELETE CASCADE,
    PRIMARY KEY (id)                  
)   ENGINE=INNODB;

CREATE TABLE manager (
   id       INT     NOT NULL,
   mgr_usr_id VARCHAR(15) NOT NULL,
   mgr_name VARCHAR(25) NOT null,
   KEY         (id),
   KEY         (mgr_usr_id),
   FOREIGN KEY (id)  REFERENCES associate (idmanager)   ON DELETE CASCADE,
   PRIMARY KEY (id)
)  ENGINE=INNODB;

CREATE TABLE attendance (
   sno      INT     NOT NULL,
   manager_idmanager INT    NOT NULL,
   associate_id INT     NOT NULL,
   date_stamp DATETIME,
   state BIT    NOT NULL,
   PRIMARY KEY (sno)
)  ENGINE=INNODB;

屏幕截图

Answer 1

这是一个排序问题。例如，执行的第一条语句是

CREATE TABLE associate (

其中引用了出勤。但是，出勤 表尚未创建。切换顺序，以便引用其他表的任何表都排在最后。

或者，不要将 FOREIGN KEY 约束放在 CREATE 语句中，而是将它们放在带有 ALTER TABLE 语句的脚本末尾。考虑:

CREATE TABLE associate (
  id        INT          NOT NULL,
  idmanager INT          NOT NULL,
  emp_id    DATE         NOT NULL,
  emp_name  VARCHAR(25)  NOT NULL,

  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE attendance (
  sno               INT       NOT NULL,
  manager_idmanager INT       NOT NULL,
  associate_id      INT       NOT NULL,
  date_stamp        DATETIME,
  state             BIT       NOT NULL,

  PRIMARY KEY (sno)
) ENGINE=INNODB;

ALTER TABLE associate ADD FOREIGN KEY (id) REFERENCES associate(id) ON DELETE CASCADE;

编辑

以上只是语法。为了对所请求的问题进行建模，请考虑信息的正交性。您可能还会看到/听到“标准化”。基本概念是:只有一份信息副本。该模式应该对所有数据有一个单一授权点。例如，如果用户有生日，请确保没有辅助列也存储他们的生日；这是多余的信息，可能会导致数据错误。

这样的话，有什么关系呢？为了让对方存在，必须先有什么？可以在没有经理的情况下出席吗？经理没有出席怎么办？前者没有任何意义。在这种情况下，我实际上会使用第三个表来形成层次结构。

然后，考虑一下公司中的角色可能会发生变化。数据库架构师不应该将角色硬编码为表。考虑:

CREATE TABLE employee (
  id      INTEGER NOT NULL AUTO_INCREMENT,
  name    VARCHAR(25) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE role (
  id    INTEGER NOT NULL AUTO_INCREMENT,
  name  VARCHAR(30) NOT NULL,
  description VARCHAR(254) NOT NULL,
  PRIMARY KEY( id ),
  UNIQUE( name )
) ENGINE=INNODB;

INSERT INTO role (name, description) VALUES
  ('associate', 'An associate is a ...'),
  ('manager', 'A manager follows ...');


CREATE TABLE employee_role (
  employee_id  INTEGER NOT NULL,
  role_id      INTEGER NOT NULL,
  PRIMARY KEY (employee_id, role_id),
  FOREIGN KEY (idemployee_id)  REFERENCES employee_id (id) ON DELETE CASCADE,
  FOREIGN KEY (role_id)  REFERENCES role (id) ON DELETE CASCADE
) ENGINE=INNODB;


CREATE TABLE attendance (
   sno               INTEGER NOT NULL,
   employee_id       INTEGER NOT NULL,
   date_stamp        DATETIME,
   state             BIT NOT NULL,
   PRIMARY KEY (sno),
   FOREIGN KEY (idemployee_id)  REFERENCES employee_id (id) ON DELETE CASCADE
)  ENGINE=INNODB;

根据此架构，出勤只需要一个外键，因为每个人都是员工。员工可以担任多个角色，并且可以改变。此外，角色定义可以更改，而无需诉诸昂贵的 DDL 语句(数据定义层更改，如 ALTER TABLE)，并且可以使用简单的 DML 进行修改(数据操作层更改，如 UPDATE TABLE)。前者涉及重写表中的所有条目并更改架构，而后者涉及更改单个条目。