0

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I have provided an updated answer that ensures uniqueness across the 2 tables.
identnum will not be unique in the submodules table. Submodule1 and Submodule 2 can have the same identnum as long as they are not usable by the same module.
In that case what you want is impossible - any attempt to create a constraint against the submodules table will be prone to a race condition because there could be concurrent writes occurring on that table. Nothing prevents updating the record for submodule #2 to change its identnum, while another request is being made to link submodule #2 to a module that may already have a submodule with either the old or new identnum -- in either case you might get the wrong result because of the race condition.
0

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) );
Improve this answer

7 Comments

This is not correct... you have ignored the variable identNum completely.
Maybe because it is redundant? What purpose does it serve?
It's not redundant. The identnum will uniquely identify the submodule in the scope of the module that can use it. For a given module all of it's submodules will have a unique identnum. Submodule1 and Submodule2 may have the same identnum as long as they are not usable by the same module. It's a bit of a weird system but I did not design the structure of the data this way. I just have to work with it.
If you insist on adding it: add it, and put a UNIQUE constraint on (module,identnum)` Do note that you are entering MVD-land here.
Can I add a constraint between a column in one table and a column in another table? ie. between modules.id and submodules.identnum?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.