Bucky Bucky - 9 months ago 47
SQL Question

Make Unique key work with NULL values?

I have created chat application and its chat seen sql look like below

chat_seen --table name
from_user | to_id | group_id | unseen_count

It works like this:When group_id is NULL it means that this is one to one chat,if group_id is not null from_user is final user which wrote message and to_id is user who is group member.I have Foreign key on group_id and it reference to group table,and I have Unique key for 3 column(from_user,to_id,group_id),When group_id is null it should work because from_user and to_id should be unique in one to one chat ,but in the group chat it should be unique per group_id,this will work if Unique key works with NULL VALUES because if it works it will count group_id NULL values as group_id and per NULL group from_user and to_id will be unique.I cant change group_id to anything because it has foreign key.

I also want to achieve that if group_id is not null to_id should has Foreign key and it should reference to group member,group_id is not NULL it should reference to user_id in user table.

Updated version with examples

When someone in the group write message per message I insert or update(if exists) to chat_seen table for each user,it means that if Group contain 20 people per each message I will update or insert 20 column to notify them later if they dont see chat.And I update unseen_count per each user.

What should I do?any suggestion?

Answer Source

I have solved this problem with a little bit hack.When one to one chat insert to chat_seen table first I check from_user is null or not because I allowed from_user can be null(I will explain it later).Then I have created from_user and to_id Unique key.Now I have 2 Unique key 1)from_user and to_id and another is 2)to_id and group_id(I have delete from_user from this unique key).When user insert to chat_seen table with group_chat mod,I insert null to from_user because I want to ignore 1) Uniuqe key for groups,if group is null it means one to one chat 2) unique key will be ignore.