Postgres: Not-null constraint with two foreign keys

Question

I'm new to database design and wanted to try doing a simple chat web app to practice the skill.

I'm trying to make a Messenger.com like chat application where users can send messages to contacts and groups.

I found this nice tutorial but I'm having a problem with my "messages" table: http://www.vertabelo.com/blog/technical-articles/database-model-for-a-messaging-system

The idea was, that when a message is sent to a user the "reciever_group_id" is NULL and when a message is sent to a group the "reciever_user_id" is NULL. However, postgres won't let add a message to the Messages table, because the foreign keys cannot be NULL, that is it violates the NOT-NULL constraint for reciever_user_id OR reciever_group_id.

Any tips?

CREATE TABLE users (
  id serial primary key,
  username character varying(32) NOT NULL UNIQUE,
  password character varying(255) NOT NULL,
  name character varying(64) NOT NULL,
  image character varying(255),
  active int NOT NULL DEFAULT 0
);

CREATE TABLE groups (
  id serial primary key,
  name character varying(255) NOT NULL
);

CREATE TABLE group_users (
  id serial primary key,
  user_id serial references users(id),
  group_id serial references groups(id)
);

CREATE TABLE messages (
  id serial primary key,
  user_id serial references users(id),
  reciever_user_id serial references users(id),
  reciever_group_id serial references groups(id),
  body text
);

user330315 · Accepted Answer · 2018-06-04 14:28:39Z

4

You want a check constraint that ensures that exactly one of those two columns contains a non-null value.

CREATE TABLE messages (
  id serial primary key,
  user_id integer,
  receiver_user_id integer,
  receiver_group_id integer,
  body text,
  constraint check_group_user check (
      (receiver_user_id is null and receiver_group_id is not null) 
   or (receiver_user_id is not null and receiver_group_id is null) )
);

Also: do not define the FK columns as serial that will auto-generate different ids because each column uses another sequence.

Foreign key columns should be defined with the base data type of the referenced column. So integer instead of serial. serial is not a real data type

answered Jun 4, 2018 at 14:28

user330315

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

1 Comment

ViktorMS Over a year ago

Thanks! serial references was my problem and integer references works.

Doodloo · Accepted Answer · 2020-08-04 20:58:28Z

2

You could also go with a constraint that uses NUM_NONNULLS(...).

Something like:

CREATE TABLE messages (
  id serial primary key,
  user_id integer,
  receiver_user_id integer,
  receiver_group_id integer,
  body text,
  constraint check_group_user check (
    NUM_NONNULLS(receiver_user_id, receiver_group_id) = 1
  )
);

answered Aug 4, 2020 at 20:58

Doodloo

9195 silver badges19 bronze badges

Collectives™ on Stack Overflow

Postgres: Not-null constraint with two foreign keys

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related