CODEWITHSUNDEEP
sqldatabaseoracle-databaseforeign-keysunique
Tim
Tim

Reputation: 255

Two foreign keys referring to one table

I am building the backend of a chat system for which I have a Message table which looks like this:

enter image description here

The problem is that I need 2 foreign keys pointing on the same table ID.

No problem, but in the case, that a user sends multiple messages, the database says

unique constraint violated

Example:

13  Testmessage1    01.06.17 08:15:01,456554000    2 (sender_id)    23 (receiver_id)
14  Testmessage2    01.06.17 07:09:08,517000000    2 (sender_id)    23 (receiver_id)

The two last identifiers are the User Id's which are both the same!

How can I handle that?

Upvotes: 1

Views: 871

Answers (3)

G.y
G.y

Reputation: 31

The key is not to violate the uniqueness constraint, I think the problem is not here, you need to provide a complete table structure, I do not understand one thing, why do we have to set up a foreign key, I think this will reduce the system flexibility and maintainability, increase a little difficulty for the development, I think we should get rid of the foreign key.

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5926

The design seems fine to me, but the key you define on that Messages table should be different. It's perfectly fine that user X sends a message to user Y multiple times, so I see two options for you to define the key:

  • sender_id / receiver_id / gendate: it shouldn't happen that two messages from the same user to the same user are sent at the same time
  • ID: you're already providing a surrogate key, you can use that

Edit (further explaination)

When you define a table, you can also define uniqueness constraints on it.

Let's assume your table has a unique key defined as (sender_id, receiver_id); with that, you're telling the dbms "hey, don't let me insert two messages with the same values of sender_id and receiver_id, because those are the criterias I want to use to uniquely identify a row". This way, whenever you pick a sender and a receiver, you're sure that you will get no more than one message.

If you have a row with values 2 and 23 in your table, trying to insert another one with those same values results in that error message.

My first option says that if you change that unique key to also include gendate instead, the dbms will assure you that there won't be two rows with the same sender_id, receiver_id AND gendate values. The rows that are causing your error would then be considered different.

The second option says that if your unique key is ID, you can even have multiple messages between the same users at the same time, as long as you generate a different ID value for each row you insert.

Upvotes: 0

Tim
Tim

Reputation: 255

Problem Solved I looked at my Constraints and i saw, that i have combined the unique constraints in my Django Model. I deleted that, and migrated my Database, and now i can create multiple Messages. Thanks for your answers!

Upvotes: 1

Related Questions