0

Suppose I have 2 database tables user and topic.

Users can have multiple topics, and a topic can have multiple users associated with it. It is a many to many relationship.

I have a user_id PK in the user table and a topic_id PK in the topic table.

I know I can enforce the many to many relationship by using a 3rd child table (call it linking_table) which has a FK to the user_id and a FK to the topic_id.

Suppose I am OK with users being created at will, but I do not want someone to be able to create a topic without it being associated with a user. In other words, I want it such that a topic row should never exist without there being a row in the linking table which links a user to that topic. On the other hand, I also don't want there to be a row in linking_table between a user and a topic which does not exist yet.

It seems I want it to be that you cannot insert a row into topic without also inserting a row into linking_table at the same time.

Is this kind of constraint possible or even reasonable? Without this constraint, it would be possible to insert into the topic table, and not create a link in linking_table at all.

Edit:

Something has occurred to me. It doesn't matter if a topic is created and not associated with a user. I will only be accessing backend data via a user, and the linking table is what will link a user to a topic, so even if theoretically there are topics not connected to users, it doesn't matter from any practical perspective. It's similar to a detached linked list node. Since it isn't referenced, it doesn't matter if it exists, assuming I do some sort of clean up on the table and remove any orphaned topics.

Ian Q
  • 1
  • 1
  • Most current SQL DBMS products don't allow constraining this declaratively; you would use triggers to enforce desired states. Please tag your DBMS. To update multiple interconstrained tables use a transaction in which you suspend declared constraints. Have you googled many clear, concise, specific versions of (tags plus) your question, keeping one as title? Because this is a faq. – philipxy Jul 10 '17 at 20:37
  • Hey philipxy, thanks for the response. I'm using PostgreSQL, and I'll tag that. I have done a decent amount of searching, but not with Postgres in the search. I guess I'm looking for any guidance how to approach the issue, not assuming that it's possible or reasonable to force a constraint for 2 inserts at once. I'm not super experienced with this topic, so I'll look into "triggers". What do you mean by "suspend declared constraints"? Also, it's worth noting – Ian Q Jul 10 '17 at 23:38
  • Also, it's worth noting I will be the only party interacting with the database and therefore can ensure that any time a topic is created, it will be associated with a user via the linking table, but this was a question still occupying me because of the following: if it was a 1 to many relation, many topics for a user, then I would only need the 2 tables, and no linking table. Then I would be able to enforce what I want here; you could only create a topic if you associated it via a non-null FK with a user. But since it's a many to many, it seems odd that I can't enforce this in some way. – Ian Q Jul 10 '17 at 23:43
  • Re "suspend" ALTER TABLE CHECK/NOCHECK CONSTRAINT. No current SQL DBMSs implement arbitrary declarative constraints, ie ASSERTIONs. Just PKs, UNIQUE, NOT NULL, CHECK (typically only checked per insert), FK. Some allow SELECT in CHECK (although not necessarily soundly under concurrency). [Why don't DBMS's support ASSERTION](https://stackoverflow.com/questions/6368349/why-dont-dbmss-support-assertion/6372101#6372101) If you want to use a tool well read the manual. You appear to need to read an intro to SQL & DB design. Also as I told you google multiple clear, concise, specific phrasings. – philipxy Jul 11 '17 at 09:00
  • Thanks for the info. – Ian Q Jul 11 '17 at 16:06

1 Answers1

0

Regarding your statement : In other words, I want it such that a topic row should never exist without there being a row in the linking table which links a user to that topic. --> This is not possible as it is not possible to Insert in both the tables simultaneously.

Regarding your statement : "On the other hand, I also don't want there to be a row in linking_table between a user and a topic which does not exist yet." --> You can add foreign key in the "linking_table" referencing to "topic_id" PK column of "topic table".

Tanmoy Saha
  • 48
  • 6