14 lines
501 B
SQL
14 lines
501 B
SQL
-- Enforce undirected connection uniqueness and prevent self-edges
|
|
|
|
-- Prevent self-edge (A == B)
|
|
ALTER TABLE "Connection"
|
|
ADD CONSTRAINT "Connection_no_self_edge"
|
|
CHECK ("personAId" <> "personBId");
|
|
|
|
-- Unique undirected pair using functional index on LEAST/GREATEST
|
|
-- Ensures only one edge exists for a given unordered pair {A,B}
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "Connection_undirected_pair_unique"
|
|
ON "Connection" (
|
|
(LEAST("personAId","personBId")),
|
|
(GREATEST("personAId","personBId"))
|
|
); |