• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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.
0
mfony
Asked:
mfony
  • 3
  • 2
1 Solution
 
Anubis2005Commented:
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.
0
 
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?

or

-create a 'Unique Index' on both the columns

Which would be the best way of going about it?
0
 
Anubis2005Commented:
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)
0
 
mfonyAuthor Commented:
The fund and date information will all be pre-set
0
 
Anubis2005Commented:
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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