• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

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

 
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
 
Sheriff
Posts: 3752
62
Netbeans IDE Oracle Chrome
  • 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
Netbeans IDE Oracle Chrome
  • 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
Netbeans IDE Oracle Chrome
  • 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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!