non UNIQUE constraints

Question

So, I got in this situation: I create a table (let say model_a), in this table I have 2 cols (let say col1 and col2). I need a constraint that satisfy this reality:

model_a

col1 | col2
-----+-----
  1  |  1  //first row
  2  |  1  >> ok
  1  |  1  >> ok
  1  |  2  >> can not insert this row

It's not UNIQUE constraint, you can duplicate the first row.

The only one time this constraint active is col1 is the same but col2 is different.

Need help :smile: Thanks a lot!

There is no such thing as a "first row" in a relational database. Tables represent un-ordered sets. — user330315
– user330315, Commented Oct 26, 2020 at 10:05
@a_horse_with_no_name . . . I don't think the OP means "first row in the table" but simply "first row in the example" where the columns are equal. — Gordon Linoff
– Gordon Linoff, Commented Oct 26, 2020 at 11:33

GMB · Accepted Answer · 2020-10-26 10:07:18Z

1

I am thinking of a unique index on the least/greatest value of both columns, that applies only to rows where the values are not equal:

create unique index myidx 
    on model_a (least(col1, col2), greatest(col1, col2))
    where (col1 <> col2)

Demo on DB Fiddle:

insert into model_a (col1, col2) values (1, 1); -- ok
insert into model_a (col1, col2) values (2, 1); -- ok
insert into model_a (col1, col2) values (1, 1); -- ok

insert into model_a (col1, col2) values (1, 2);
-- ERROR:  duplicate key value violates unique constraint "myidx"
-- DETAIL:  Key (LEAST(col1, col2), GREATEST(col1, col2))=(1, 2) already exists.

answered Oct 26, 2020 at 10:07

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

astentx Over a year ago

I don't feel sample data is enough to make any decision about algo, but looks like when the "first" row is 1, 2 it is allowed to have 2,1. I didn't understand which "sameness" is checked: col1 value of new row with col1 of any "before" row or col1 and col2. Considering the tables like spreadsheet, not an unordered set in DBMS.

GMB Over a year ago

@astentx: the way I understood the question, "first" refers to the order in which rows are added to the table, as shown in my demo. The "first" row could very well be (1, 2), in which case inserting (2, 1) later on would not be possible.

Collectives™ on Stack Overflow

non UNIQUE constraints

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related