• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Junction table - PK - unique index

Hello,

I have a junction table with composite PK on 3 fields...
[fldA], [fldB], [fldC]
I also need values to be unique across 4 values...
[fldA], [fldB], [fldC], [fldD]
I cant add the [fldD] column to the pk cause then 2 records could have the same data for [fldA], [fldB], [fldC], and that would be bad data.

Was going to add another unique constraint using all 4 fields.
Is that the best most efficent way to handle this?
Table will most likely have few records from 5 - 100.

Thanks!


0
krazykoder
Asked:
krazykoder
1 Solution
 
UnifiedISCommented:
if your index requires a,b,c to be unique, you have already ensured that a,b,c,d will be unique
0
 
krazykoderAuthor Commented:
OH - duh :)  
Sorry i don't think asked my question properly.
Using generic fields in my example is confusing me more.
Let me try again.

Composite PK is on Strategy, Type & Priority
So far everything is good they need to be unique.
Problem is that it would be considered bad data to have the Size field be the same as in the example below.

Strategy      Type      Priority      Size
1      1      1      1

1      1      2      1

i cant just add Size to the PK cause then that would let there be a dup Priority like...

Strategy      Type      Priority      Size
1      1      1      1

1      1      1      2

So i was thinking of keeping PK the way i have it on Strategy, Type & Priority
then adding a unique constraint on all 4 Strategy, Type & Priority, Size

now i can ask...
Is this the best & most efficent way to handle this situation or is there a better way to accomplish it?

Thanks!
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Is this the best & most efficent way to handle this situation or is there a better way to accomplish it?>>
If you want to implement uniqueness over a specific number of columns, implement only one index (clustered or non clustered) and a unique constraint over that index....No need to multiply indexes only in the purpose of implementing the constraint...

Hope this helps...

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
krazykoderAuthor Commented:
Thanks for the response
I don't fully understand what you mean though, or at least not how it applies to my situation.

In my example the 1st 3 field need to be unique, but so do those 3 plus a fouth field.

I can't make one unique constraint / index across all 4 fields cause then there can be dups across the 1st 3.  And on the other hand i can't just make 1 unique constraint / index on the 1st 3 cause there can be dups across all 4.

Only way i can see to do it is 2 seperate constraints / indexes unless there is some syntax or method I'm not aware of.  This is why I am asking my question.    My concearn was overkill by having 2 indexes so similar (i.e they both contain the 1st 3 fields, but one has an extra field).  But if thats the only way - so be it.

My understanding is that a unique index & unique constraint are ultimatly the same thing.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<My understanding is that a unique index & unique constraint are ultimatly the same thing.>>
That's because SQL Server does not implement *unicity* constraint otherwise than by indexing...In fact both are different concepts but, when using SQL Server, you may consider them synonyms...

Hope that helps...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Only way i can see to do it is 2 seperate constraints / indexes unless there is some syntax or method I'm not aware of.  This is why I am asking my question.    My concearn was overkill by having 2 indexes so similar (i.e they both contain the 1st 3 fields, but one has an extra field).  But if thats the only way - so be it.>>
Maybe some extra explanations may help...You need to ask yourself what is yourlogical primary key (unique identifier) the table...*Only* the primary key *must* to be unique.  There is no need for 2 unique identifiers in the same table.  If you believe that your primary key is on the 3 first columns then implement uniqueness on the 3 columns only.  If you believe that your primary key is on the 4 columns then implement uniqueness on the 4 columns...


0
 
Scott PletcherSenior DBACommented:
>> Problem is that it would be considered bad data to have the Size field be the same as in the example below.
Strategy      Type      Priority      Size
1                  1            1              1
1                  1            2              1 <<

The same for stategy and type (only)?  Then you need a separate unique constraint/index on (strategy, type, size).
The same size for a given strategy only?  Then you need a separate unique constrain/index on (strategy, size).
0
 
krazykoderAuthor Commented:
<<The same for stategy and type (only)?>>  Yes

Basically you are defining the different Sizes for a given (Strategy / Type) combination, but there needs to be a priority so we can determin what Size to give out 1st if a given  (Strategy / Type) combination has multiple Sizes defiend for it in this table.  So depending on how you look at it my Primary Key should be on either...
Strategy, Type & Priority
or
Strategy, Type & Size

The more i think about it, Strategy, Type & Size makes a little bit more sense so lets say that is my PK.  (It just flip flops Priority & Size in my previous example)

So revised example now looks like this...

Strategy      Type      Size         Priority
1                  1            1              1
1                  1            2              1 <<

So I could be done here, but nothing is preventing the person setting up the db from accidentally using the same priorty.  So as shown above the user defined 2 different sizes for the same (Strategy / Type) combination.  No problems there.  The problem is they tried to give them both the same priority.

So if my PK is...
Strategy, Type & Size
I need to create another unique constraint on...
Strategy, Type & Priority
Right?

ScottPletcher you basically answered my question - i just flopped it around righ now :)
I was originally thinking I needed to put the extra constraint on all 4 fields, which would proabbaly work just fine, but your reply made me realize i dont.  I'm assumming leaving that 4th field off of my 2nd constraint is actually better for performance.  This is exactly why I asked the question to begin with.

Thanks again for all the responses.
I appologize if i wasnt as clear as I could have been from the start.

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<So depending on how you look at it my Primary Key should be on either...
Strategy, Type & Priority
or
Strategy, Type & Size>>
The most important is that you have found what you are looking for.  It seems these two are candidate keys...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now