constraints that are unique / references columns of another table in postgres

Question

I have two tables. In table_b, I want to reference two columns (not primary key) from table_a (as when I join the two tables, I want to join on both product_id and group_id). I also want the combo of those two keys to be unique. Is this foreign key essentially a composite key if I don't have the primary key id on table_b? Does it make sense for me to create this foreign key in table_b (even though its not referencing primary keys from table_a?

Does it make sense to have two constraints like below on table_b?

CREATE TABLE table_a(
  id SERIAL PRIMARY KEY,
  product_id VARCHAR, // can be null
  group_id VARCHAR NOT NULL,
)

CREATE TABLE table_b(
  id SERIAL PRIMARY KEY,
  product_id VARCHAR NOT NULL,
  group_id VARCHAR NOT NULL,
  CONSTRAINT fk_key FOREIGN KEY(product_id, group_id) REFERENCES table_a(product_id, group_id)
  CONSTRAINT unique_id UNIQUE (product_id, group_id);
)

Note that table_a product_id can be null, but not that of in table_b. All references from table_a in table_b will only include if product_id and group_id is not null. Thanks!

I am stuck at this sentence: "I want to reference two columns (not primary key)." I cannot imagine a scenario where referencing two text columns would be preferable to a nice, simple, integer primary key. — Gordon Linoff
– Gordon Linoff, Commented Feb 3, 2021 at 21:05

sticky bit · Accepted Answer · 2021-02-03 20:12:32Z

2

Foreign keys need to reference keys but not necessarily primary keys. So you'd need to include a unique constraint in table_a.

CREATE TABLE table_a
             (id serial,
              product_id varchar,
              group_id varchar
                       NOT NULL,
              PRIMARY KEY (id),
              UNIQUE (product_id,
                      group_id));

CREATE TABLE table_b
             (id serial,
              product_id varchar
                         NOT NULL,
              group_id varchar
                       NOT NULL,
              PRIMARY KEY (id),
              FOREIGN KEY (product_id,
                           group_id)
                          REFERENCES table_a
                                     (product_id,
                                      group_id),
              UNIQUE (product_id,
                      group_id));

edited Feb 3, 2021 at 20:12

answered Feb 3, 2021 at 19:46

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

2 Comments

Bohemian Over a year ago

varchar(1) is not the same as varchar . It’s also unlikely to be useful; a variable length string with a maximum length of 1 can’t hold many distinct values.

sticky bit Over a year ago

@Bohemian: Habit. I find varchars without length confusing. Changed it anyway.

Bohemian · Accepted Answer · 2021-02-03 20:30:53Z

0

Don’t create a FK from table_b to table_a, because there actually isn’t such a relationship due to table_a allowing product_id to be null.

So just

create table table_a (
    id serial primary key
    product_id varchar,
    group_id varchar qnot null,
    unique (product_id, group_id)
    foreign key (product_id, group_id)
      references table_b (product_id, group_id)
)

It looks likely that table_b should be folded into table_a, which I recommend you do; 2-way FKs are usually a design smell.

edited Feb 3, 2021 at 20:30

answered Feb 3, 2021 at 20:21

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

constraints that are unique / references columns of another table in postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related