Newbie here having trouble wiring up some tables for a game’s leaderboard. Would appreciate help! I have 3 tables; here’s an ER diagram:
┌─────┐ ┌─────┐
│ IPs ├─||─────────────────|<─┤Names│
└──┬──┘ └──┬──┘
│ │
│ │
│ │
│ │
│ │
│ ┌──────┐ │
└─||───O<─┤Scores├─|O──────||─┘
└──────┘
Basically, I’d like to have each IP
have multiple name
s, and each score map to an IP
‘s name
.
However, I run into this error
ERROR: there is no unique constraint matching given keys for referenced table "names"
when I try:
CREATE TABLE IPs(
ip CIDR NOT NULL,
PRIMARY KEY(ip)
);
CREATE TABLE Names(
name VARCHAR(25) NOT NULL,
ip CIDR NOT NULL,
CONSTRAINT ip_fk FOREIGN KEY(ip) REFERENCES IPs(ip),
PRIMARY KEY(name, ip)
);
CREATE TABLE Scores(
score INT NOT NULL,
name VARCHAR(25) NOT NULL,
ip CIDR NOT NULL,
created_at TIMESTAMP NOT NULL,
CONSTRAINT name_fk FOREIGN KEY(name) REFERENCES Names(name),
CONSTRAINT ip_fk FOREIGN KEY(ip) REFERENCES IPs(ip),
PRIMARY KEY(score, name, ip)
);
How should I wire these tables up?
2
Answers
I also found a possible solution that uses natural keys. Instead of creating 2 separate foreign keys in
Scores
, I instead create a single composite foreign key:You are trying to reference non-unique columns. From the
Scores
table, you are referencing bothname
andip
from theNames
table, which combined are unique, but you are referencing them separately.I would highly recomend adding autoincrement identifiers to each table instead of using a combination of every single column as primary key.
Primary keys are suposed to be a light short unique value that identifies a full row, using the full row as primary key has bad performance and makes foreign key references unnecesarily complex, producing a lot of duplicated data in your database.
In the
Scores
table, it is not necesary to add a reference to theIPs
table, it is enough to reference theNames
table, because theNames
table already contains theIPs
reference.