CODEWITHSUNDEEP
postgresql
Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2217

How to make constraint for foreignkey in Postgres

I mainly work with Django but this issue I believe I can resolve only on Postgres level. I am a beginner in SQL, so that if question is elementary -please forgive me.

I have following DDL for users table:

create table users_user
(
    id           serial                   not null
        constraint users_user_pkey
            primary key,
    password     varchar(128)             not null,
    last_login   timestamp with time zone,
    is_superuser boolean                  not null,
    is_staff     boolean                  not null,
    is_active    boolean                  not null,
    date_joined  timestamp with time zone not null,
    email        varchar(254)             not null
        constraint users_user_email_key
            unique,
    first_name   varchar(30)              not null,
    last_name    varchar(150)             not null,
    user_country varchar(2)
        constraint country_code_within_list_of_countries_check
            check ((user_country)::text = ANY
                   ((ARRAY [elements of array are omitted for brevity])::text[])),
    master_id    integer
        constraint users_user_master_id_c8e42c6f_fk_users_user_id
            references users_user
            deferrable initially deferred,
    constraint users_user_first_name_last_name_622a9092_uniq
        unique (first_name, last_name)
);

master_id row here is foreign-key referencing same table. Logical reason for this is that I want to separate all users in this table to masters and slaves ( I am not support slavery but just for better understanding lets call them masters and slaves). In this scenario master always have master_id field = null, and slaves = integer(id of master). For example:

user1, id =1, master_id = null   --master
user2, id=2, master_id = 1 --slave
user3, id=3, master_id = 1  --slave

Question is – is it possible to make a constraint or check-constraint ( 1 or 2 separated constraints, no difference) in a such way to suit 2 conditions:

1) Slave can’t have it’s own slaves.

2)Master of the slave cant be slave himself.

Thank you in advance.

Upvotes: 0

Views: 43

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248135

You could have two foreign keys:

  • slave_id points to the slave (can be NULL for masters)
  • master_id points to the master (can be NULL for masters)

That is not very pretty because of the redundancy, but it allows you to

ALTER TABLE users_user
   ADD CHECK (master_id IS NULL OR slave_id IS NULL);

which would satisfy your requirement.

Upvotes: 1

Related Questions