CODEWITHSUNDEEP
postgresqlconstraints
Thomas
Thomas

Reputation: 6710

One-way multi column UNIQUE constraint

I have a system in which I am trying to describe event-based interactions between two targets. In our system, an event (interaction) has a "source" and a "target" (basically who [did what] to whom):

-- tried to remove some of the "noise" from this for the sake of the post:
CREATE TABLE interaction_relationship (
    id integer CONSTRAINT interaction_pk PRIMARY KEY,
    source_id integer CONSTRAINT source_fk NOT NULL REFERENCES entity(id),
    target_id integer CONSTRAINT target_fk NOT NULL REFERENCES entity(id),

    -- CONSTRAINT(s)
    CREATE CONSTRAINT interaction_relationship_deduplication UNIQUE(source_id, target_id)
);

Constraint interaction_relationship_deduplication is the source of my question:

In our system, a single source can interact with a single target multiple times, but that relationship can only exist once, i.e. if I am a mechanic working on a car, I may see that car multiple times in my shop, but I have only one relationship with that single car:

id source_id target_id
a 123abc 456def
b 123abc 789ghi

Ideally, this table also represents a unidirectional relationship. source_id is always the "owner" of the interaction, i.e. if the car 456def ran over the mechanic 123abc there would be another entry in the interaction_relationship table:

id source_id target_id
1 123abc 456def
2 123abc 789ghi
3 456def 123abc

So, my question: does UNIQUE on multiple columns take value order into consideration? Or would the above cause a failure?

Upvotes: 0

Views: 88

Answers (1)

Bergi
Bergi

Reputation: 665546

does UNIQUE on multiple columns take value order into consideration?

Yes. The tuple (123abc, 456def) is different from the tuple (456def, 123abc), they may both exist in the table at the same time.

That said, you might want to remove the surrogate id from the relationship table, there's hardly any use for it. A relation table (as opposed to an entity table, and even there) would do totally fine with a multi-column primary key, which is naturally the combination of source and target.

Upvotes: 1

Related Questions