[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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.
0
dwcummings
Asked:
dwcummings
1 Solution
 
Kyle AbrahamsSenior .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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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