CODEWITHSUNDEEP
mysqlsqlconstraints
Valentino Ru
Valentino Ru

Reputation: 5052

SQL constraint: check if a bi-referenced table has common value

Let's say I have following three tables:

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 */
)

As it implies, a transfer should reference two accounts, but I want to be sure that the two accounts belong to the same person. How would I solve this when creating the table? Is this even possible?

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Unfortunately, you need to repeat the user_id in the transfer table:

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

Upvotes: 2

Related Questions