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
);

```

Answer: 
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](https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL)
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
  )
);

```