0

I am creating a database for handbag shopping website in which i have a table called dbo.ProductMatching 

CREATE TABLE [dbo].[ProductMatching](
[ProductMatchingID] [int] NOT NULL IDENTITY, -- This can be Made Primary Key but i want to use composite keys
[MainProductID] [int] NOT NULL,
[MainProductColourID] [int] NOT NULL,
[ReferenceProductID] [int] NULL,
[ReferenceProductColourID] [int] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UpdatedOn] [datetime] NOT NULL
) ON [PRIMARY]

What i want to do is Make (MainProductID,MainProductColourID) unique and for each this combination i want to make combination of (ReferenceProductID,ReferenceProductColourID) unique also.

e.g suppose i have combination of (MainProductID,MainProdcutColourID) = (1,1) it referes to (ReferenceProductID,ReferenceProductColourID) = (2,2) Then (1,1) can not refer to another (2,3) combination.. i cant make the whole four keys composite keys because it would allow reference (1,1) to (2,3) combination..

i know i can just make exists statement when inserting data or make a before insert trigger or update trigger for data consistency but what i want to know if this can be done with using composite keys.. if not what are other available options...

Krunal Parekh
  • 81
  • 10
  • Not clear are you saying you know the allowable matches between Product and reference or that a Product can only have 0-1 linked references? – Tony Hopkinson Mar 14 '14 at 17:35
  • yes the matches will be inserted manually.. well this table is used for showing products similar to product the user is purchasing (products you also might like) in this similar product suppose user is purchasing green handbag and in similar products a red jacket is referenced then there should not be another reference to jacket with different colour (suppose black). The setting that green handbag will show red jacket as similar product will be predefined by admin... – Krunal Parekh Mar 14 '14 at 18:37
  • Why do you want to use an [_evil_](http://codebetter.com/jeremymiller/2007/02/01/composite-keys-are-evil/) composite primary key? You can always make unique constraints. Check: http://stackoverflow.com/a/4737412/122195 – thepirat000 Mar 14 '14 at 23:31
  • @thepirat000 Why would you waste a separate column for PK if you have a natural composite key? – paparazzo Mar 15 '14 at 01:24
  • @Blam because JOIN conditions with child tables are much simpler, DB must maintain two indexes rather than one, it's more clean and easier to maintain, and the key aren't coupled to business. But at the end it's a matter of taste... – thepirat000 Mar 15 '14 at 01:42
  • @thepirat000 Wrong on all counts. Composite key is one index. If you make it a unique constraint and add a PK then 2 index. You want keys tied to business. It is not just taste it is 3rd normal form. – paparazzo Mar 15 '14 at 01:57
  • Natural versus Surrogate. There are pros and cons on both strategies, we can discuss this forever. I would like to see your beautiful queries when having a composite key of 3 or 4 fields referenced by foreign keys. Take a look at the following posts: http://www.agiledata.org/essays/keys.html, http://www.techrepublic.com/blog/10-things/10-tips-for-choosing-between-a-surrogate-and-natural-primary-key/2362/, http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/ – thepirat000 Mar 15 '14 at 03:36
  • @thepirat000 I will take 3NF every time and it has stood up to the test of time. So what if the join is on multiple conditions? I will take that over multiple joins every time. – paparazzo Mar 15 '14 at 14:09
  • Each to their own @Blam, its not a hard and fast rule but in this case I'd be a lot more comfortable with surrogates. – Tony Hopkinson Mar 17 '14 at 14:12

1 Answers1

1

If I understand the problem then two simple unique constraints

MainProductID,MainProductColourID

and

MainProductID,ReferenceProductID

And I would use
MainProductID,MainProductColourID as a composite PK
(that will satisfy that unique constraint)

If that is wrong then please show more examples that are correct
And more that are incorrect with a reason

paparazzo
  • 44,497
  • 23
  • 105
  • 176