Postgresql, references to unique constraint

Question

I'm running PostgreSQL 9.4 and have the following table:

CREATE TABLE user_cars (
    user_id SERIAL REFERENCES users (id) ON DELETE CASCADE,
    car CHARACTER VARYING(255) NOT NULL,
    CONSTRAINT test UNIQUE (user_id, car)
);

The table allows a user to have multiple cars, but only use the car name once. But other users may have the same car name.

I would like to have another table with references to the unique constraint test, and have tried stuff like:

CREATE TABLE mappings (
    other_id CHARACTER(9) REFERENCES other (id) ON DELETE CASCADE,
    user_cars  REFERENCES user_cards (test) ON DELETE CASCADE
);

But that fails "obviously". I would like to make sure that other_id only have a single references to a user_car entry.

So to explain, how can I in table mappings have a references to test from table user_cars. This is the thing that fails currently:

    user_cars  REFERENCES user_cards (test) ON DELETE CASCADE

Gordon Linoff · Accepted Answer · 2018-04-23 11:15:43Z

1

Don't use composite foreign key references, if you can avoid it. Just add a unique id to the table:

CREATE TABLE user_cars (
    user_car_id serial primary key,
    user_id int REFERENCES users (id) ON DELETE CASCADE,
    car CHARACTER VARYING(255) NOT NULL,
    CONSTRAINT test UNIQUE (user_id, car)
);

Then mappings is simply:

CREATE TABLE mappings (
    mapping_id serial primary key,
    user_car_id int references user_cars(user_car_id) on delete cascade,
    other_id CHARACTER(9) REFERENCES other (id) ON DELETE CASCADE,
);

answered Apr 23, 2018 at 11:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Alfred Balle Over a year ago

Don't think I quite understand. The mappings table second column should refer to the unique combination of user_id and car, not just one of the columns.

Alfred Balle Over a year ago

I cannot make the user_car_id a primay key, because then other users wouldn't be able to use that name.

Alfred Balle Over a year ago

Wait, you are correct. Missing the fact that you made a new primary key - of course. Simple. thank you.

Rahul Jain · Accepted Answer · 2018-04-23 07:44:43Z

0

If car should be unique, add UNIQUE constrain only on car column. If user should be unique, add UNIQUE constrain only on user column.

If you add UNIQUE constrain on combination, then there will be duplicate values in the table.

UPDATE: You can add multiple constraints on single column. With Foreign key add UNIQUE constraint as well on user_cars column in mapping table.

edited Apr 23, 2018 at 7:44

answered Apr 23, 2018 at 7:22

Rahul Jain

1,3997 silver badges16 bronze badges

1 Comment

Alfred Balle Over a year ago

I don't mind that user has several entries for car, but user cannot have multiple entries with same car name. Therefor the constraint on user_id and car. This seems to work fine. My problem is in the mappings table and how I can reference to that unique constraint.

Collectives™ on Stack Overflow

Postgresql, references to unique constraint

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related