Calculated identity field

I'd like to make a table that insures that I cannot have duplicate records based on a fund number and a date. Can I create an indentity field that a calculation between the two? With a single integer identity field I could have several records that have the same fund number and date.

Thanks for the help.
Who is Participating?
Anubis2005Connect With a Mentor Commented:
In that case then, you can set the primary key on both fields.  Open up Enterprise Manager and on the table go to design view and highlight both columns and click the 'Primary Key' button.  This will set the primary key (which must always maintain uniqueness) on both fields so no number/date combination will be the same.

Alternatively, you can use the "Manage Indexes" function to edit an already existing "Primary Key" index or use an ALTER script in query analyzer.
You need to create a unique index on both fields.  I would imageine you have the two fields listed as below:

FundNumber int
FundDate datetime

If so, you can create a 'Primary Key' on both of these, otherwise you can create your primary key on just the 'FundNumber' column and then create a 'Unique Index' on both the columns to ensure there are no duplicates.

Hope this helps.
mfonyAuthor Commented:
You say:

-you can create a 'Primary Key' on both of these

Does that mean identity field based on a calculation of fundnumber and funddata?


-create a 'Unique Index' on both the columns

Which would be the best way of going about it?
In order to give you the best answer I have to ask another question.. how is the data being generated?

Is it pre-set, meaning that you already have all the fund numbers and dates (a)
or are you needing to dynamically generate the fund numbers and use the current date (b)
mfonyAuthor Commented:
The fund and date information will all be pre-set
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.