Date field doesn't seem to work in a multi column primary key

In a new table, I setup a multi-column primary key consisting of a CustomerId (int) field, SalesId (int) field and a MailingDate (date) field. However, this is not preventing me from adding duplicate records into the table.

I suspect that a column of data type date cannot be used in a multi-column key or that there is a time component to this field that is preventing uniqueness?

If true, can someone suggest an alternate method of preventing duplicate records in a table with the same Customer Id, Sales Id and Mailing Date.

Thanks.
LVL 3
dwcummingsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
you could break out the date field to three month / day / year int columns.

Why include the mailing date as a primary field at all though?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I concur with above.
You could have one field with DATE datatype instead of DAYTIME for the primary key part. However if the time portion is needed for other processes you will need two field.
0
 
dwcummingsAuthor Commented:
The mailing date is the only unique column in the table, as customers can have multiple mailings from the same salesperson, but not on the same date. I like the idea of breaking out the date into 3 int columns, but have decided to add an additional column to the table to force uniqueness - that being a mailing id field. I don't need a time component.

Thanks for the help.
0
 
Scott PletcherSenior DBACommented:
There is nothing that prevents a date or datetime column from participating in the primary key or other indexes.  If it's a datetime column, then the entire time would be considered, not just the date.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Mail ID would be the way to go, this way oyu have a reference to all other info about that mailing.  Just my 2 cents.
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.