• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to create a table that is unique by a combination of cols, but not on a "single" column?

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

When col2 + col3 is repeated, it is not allowed, and it is fine.
But when only col2 is repeated, it should be allowed, but it isn't.
So could you please show me the correct way of doing this?
Thanks
Jack
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jacky Luk wrote:But when only col2 is repeated, it should be allowed, but it isn't.

Do I understand it right that the constraint should not be enforced when col3 is null?

If so, you might use function based indexes to achieve that, assuming your database supports them. Which database are you using?
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Martin,
I am using the sqlite database
Let me raise 4 examples
I insert them in this sequence


I'd like to have row 2 allowed, and row 3 not allowed, or maybe row 4 is also allowed
Just to say that a combination of col2 and col3 must be unique, or vice versa
Thanks
Jack
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jacky Luk wrote:... or maybe row 4 is also allowed

No "maybe" please. If you cannot specify the rules, neither of us will be able to come up with a solution.

Just to say that a combination of col2 and col3 must be unique, or vice versa

I'm afraid I don't get this. If a combination of columns is unique, then it is unique. No room for "vice versa", I'd say.


It might be useful if you posted an example of combination of rows which should be allowed, but isn't. So far it looks like a generic composite unique constraint to me.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin,
I've solved it by
using



It works perfectly.
Thanks for helping
Jack
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would say it is identical to the code in your first post, except that the constraint isn't named - but that doesn't affect what the constraint does.

Just a note: PRIMARY KEY is automatically NOT NULL and UNIQUE. You could drop these from the id column definition.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic