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!


krazykoderAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior 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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.