unique constraint on table

Question

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.

Answer 1

You actually have two constraints:

• {module,submodule} must be unique
• this pair must exist in allowed_pairs (where it also must be unique)

---

-- \i tmp.sql

CREATE TABLE modules(
        m_id integer PRIMARY KEY
        );

CREATE TABLE submodules(
        s_id integer PRIMARY KEY
        );

CREATE TABLE allowed_submodules(
        m_id integer NOT NULL REFERENCES modules(m_id)
        , s_id integer NOT NULL REFERENCES submodules(s_id)
        , PRIMARY KEY (m_id, s_id)
        );

CREATE TABLE used_submodules(
        m_id integer NOT NULL
        , s_id integer NOT NULL
        , PRIMARY KEY (m_id, s_id)
        ,  FOREIGN KEY (m_id,s_id) REFERENCES allowed_submodules(m_id,s_id)
        );

---

UPDATE: if you insist on keeping the (redundant, IMHO) additional key column identnum, here is how that could be added to the used_submodulestable.

---

CREATE TABLE used_submodules(
        m_id integer NOT NULL
        , s_id integer NOT NULL
        ,  PRIMARY KEY (m_id, s_id)
        ,  FOREIGN KEY (m_id, s_id) REFERENCES allowed_submodules(m_id, s_id)
        , identnum integer NOT NULL
        ,  UNIQUE (m_id, identnum)
        );

Answer 2

Set a uniqueness and not null contraint on submodules.identnum.

CREATE TABLE submodules(
    id          integer PRIMARY KEY,
    identnum    integer UNIQUE NOT NULL);

Create a composite PK on the usablesubmodules table.

CREATE TABLE usablesubmodules(
    moduleid    integer REFERENCES modules(id),
    submoduleid integer REFERENCES submodules(id)
    PRIMARY KEY (moduleid, submoduleid));

...or...

CREATE TABLE usablesubmodules(
    moduleid    integer REFERENCES modules(id),
    identnum    integer REFERENCES submodules(identnum)
    PRIMARY KEY (moduleid, identnum));

Either of the above will guarantee that you can never have an identnum associated to a module more than once.

The uniqueness constraint on identnum in the submodules table ensures that you will only ever have one submodules record for a given identnum.

The composite primary key on usablesubmodules ensures that you can never have more than one record with the same moduleid and identnum.