CODEWITHSUNDEEP
sqlsqliteforeign-keys
Jacob H
Jacob H

Reputation: 876

SQLite - Multiple Foreign Keys Referencing the Same Column

Is it possible to create a table with multiple foreign key restraints to the same column of another table?

Example:

CREATE TABLE users (
  username TEXT NOT NULL UNIQUE,
  password VARCHAR(128),
  userID INTEGER PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE friends (
  friend1 INTEGER REFERENCES users(userID),
  friend1 INTEGER REFERENCES users(userID),
  PRIMARY KEY(friend1, friend2)
);

Is it possible to do something like this (and is there a way to enforce friend1 != friend2?), or do I need a different pattern entirely?

Upvotes: 1

Views: 1655

Answers (2)

forpas
forpas

Reputation: 164194

You can define the foreign keys and the CHECK constraint that ensures that the 2 columns are different, like this:

CREATE TABLE friends (
  friend1 INTEGER NOT NULL,
  friend2 INTEGER NOT NULL CHECK(friend2 <> friend1),
  PRIMARY KEY(friend1, friend2),
  FOREIGN KEY (friend1) REFERENCES users(userID),
  FOREIGN KEY (friend2) REFERENCES users(userID)
);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Yes, but they need to have different names:

CREATE TABLE friends (
  friend1 INTEGER REFERENCES users(userID),
  friend2 INTEGER REFERENCES users(userID),
  PRIMARY KEY(friend1, friend2)
);

Here is a db<>fiddle.

Upvotes: 1

Related Questions