I have the following table definitions:
CREATE TABLE modules(
id integer PRIMARY KEY)
CREATE TABLE submodules(
id integer PRIMARY KEY,
identnum integer)
CREATE TABLE usablesubmodules(
moduleid integer REFERENCES modules(id),
submoduleid integer REFERENCES submodules(id))
Basically a table of modules and a table of submodules. Modules can only use certain submodules as defined by the usablesubmodules table. How do I define a constraint such that the identnum values for the submodules usable by any given module are unique? That is, the following query must return a set of unique identnum values for any given moduleid 'x':
SELECT identnum FROM submodules
INNER JOIN usablesubmodules ON submodules.id = usablesubmodules.submoduleid
WHERE usablesubmodules.moduleid = x
I'm using postgresql 9.6 if that matters.
You actually have two constraints:
-- \i tmp.sql
UPDATE: if you insist on keeping the (redundant, IMHO) additional key column
identnum
, here is how that could be added to theused_submodules
table.