Calculated identity field

Posted on 2006-05-26
Last Modified: 2012-06-21
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.
Question by:mfony
    LVL 1

    Expert Comment

    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.

    Author Comment

    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?
    LVL 1

    Expert Comment

    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)

    Author Comment

    The fund and date information will all be pre-set
    LVL 1

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now