1

Given the following tables

CREATE TABLE users (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  organization_id  TEXT NOT NULL,
  UNIQUE (name, email)
);

CREATE TABLE tasks (
  id               SERIAL PRIMARY KEY,
  users_id         TEXT NOT NULL,
  organization_id  TEXT NOT NULL,
  description      TEXT NOT NULL
);

Is it possible to create constraints for 'user_id' column , that i can use values form 'users' table only with the same organization_id?

2

A check constraint that references another table like that is called a FOREIGN KEY. You can do what you want with MATCH FULL

As a special note the types must be the same, so in your example tasks.users_id is text. It must be int (the underlying type of users.id).

CREATE TABLE users (
  id               SERIAL,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  organization_id  int,
  PRIMARY KEY (organization_id, id),
  UNIQUE (name, email)
);

CREATE TABLE tasks (
  id               SERIAL PRIMARY KEY,
  organization_id  int  NOT NULL,
  users_id         int  NOT NULL,
  description      TEXT NOT NULL,
  FOREIGN KEY (organization_id, users_id)
    REFERENCES users (organization_id, id) MATCH FULL
);

You can add the constriant after the fact like this..

ALTER TABLE tasks
  ADD CONSTRAINT asdf
  FOREIGN KEY (organization_id, users_id)
  REFERENCES users (organization_id, id)
  MATCH FULL;
|improve this answer|||||
  • Thank you for your reply! I know about foreign keys. But my problem is as following: for example, in 'users' table i have two users with different organization_id (id:1, organization:1 and id:2, organization:2) and then i insert new record in 'tasks' with organization_id = 1 and user = 2 it should become impossible. Sorry for my bad english :) – skyien May 19 '17 at 18:29
  • @skyien check my update, see if that's what you want. – Evan Carroll May 19 '17 at 18:34
  • 3
    BTW It breaks the logic. It allows to do something like insert into users (id, organization_id) values(1,1),(1,2) thus user.id is not unique; and it is impossible to reference to the user table from any other tables which have not organization_id column. IMO additional constraint like unique(organization_id, id) instead of altering the primary key is better... – Abelisto May 19 '17 at 19:48
  • Just depends on if you want a surrogate key, or a natural key. If the key doesn't matter and the task belongs to the user with regard for what organization he is in, I would say ditch the org requirement entirely and just references on user_id. – Evan Carroll May 19 '17 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.