mysql - SQL约束: check if a bi-referenced table has common value

Question

假设我有以下三个表:

CREATE TABLE user (
  id INTEGER NOT NULL AUTO_INCREMENT,
  /* ... */
  PRIMARY KEY(id)
)

CREATE TABLE Account (
  id INTEGER NOT NULL AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  /* ... */
  PRIMARY KEY(id),
  FOREIGN KEY(user_id) REFERENCES User(id)
)

CREATE TABLE Transfer (
  id INTEGER NOT NULL AUTO_INCREMENT,
  from_account INTEGER NOT NULL,
  to_account INTEGER NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(from_account) REFERENCES Account(id),
  FOREIGN KEY(to_account) REFERENCES Account(id),
  CONSTRAINT ???
  /* SELECT id FROM Account WHERE id=from_account == SELECT id FROM Account WHERE id=to_account */
)

顾名思义，转账应该引用两个帐户，但我想确保这两个帐户属于同一个人。创建表时如何解决这个问题？这可能吗？

Answer 1

不幸的是，您需要在传输表中重复user_id:

CREATE TABLE Account (
  id INTEGER NOT NULL AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  /* ... */
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES User (id),
  UNIQUE (user_id, id)  -- redundant but needed for the foreign key constraint
);

CREATE TABLE Transfer (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER,
  from_account INTEGER NOT NULL,
  to_account INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES User (id),
  FOREIGN KEY (user_id, from_account) REFERENCES Account (user_id, id),
  FOREIGN KEY (user_id, to_account) REFERENCES Account (user_id, id)
);