Post reply

Writing a constraing to define two different sets of uniqueness in the same table

  • October 31, 2008 at 4:51 am

    #96572

    Hi All

    A little? question on constraints 🙂

    Is it possible to have a checking constraint/trigger which can check two separate sets of fields for uniqueness within the same table depending on whether the record is of a particular type (the type being defined by a field which has one of two values)

    I could use two separate tables for these two sets of data. Let me explain, the table contains jobs, there are two types of jobs “cleaning” or “manufacturing”

    Both jobs types contain the same attributes as follows

    Attributes common to both cleaning and manufacturing jobs

    BatchNo

    ProductName

    PlannedStartDate

    PlannedEndDate

    Plant

    StartDate

    StartTime

    EndDate

    EndTime

    Attributes only found in cleaning jobs

    EquipmentNo

    CleaningType

    Attributes only found in manufacturing jobs

    Yield

    The problem is that if I only use one table (which is what I want to do for jobs) two different sets of attributes make a record unique in the case of manufacturing jobs the BatchNo and ProductName can be used as the primary key however for cleaning jobs it is the BatchNo, ProductName, EquipmentNo CleaningType and PlannedStartDate which make a record unique. Two many attributes for a primary key for my liking anyway. So I’m wondering can I use one table for all jobs and then write the constraint code to check - if the job is cleaning, attributes above must not be duplicated or if the job is manufacturing, BatchNo and ProductName combination must not be duplicated. This way I can then use an AutoNum as my primary key and all other related tables and ref to that num? but I will still ensure that my jobs are not duplicated

    Hope that not to confusing and that someone can advice.

    P.S. I plan to build the database using MS SQL and Visual Studio 2005 (first time using it so don’t know to much) only what I learned from MS Access and college.

    Thank you

    Asta 🙂

  • October 31, 2008 at 7:44 am

    #892330

    If the keys are different then in my opinion they should be different tables.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply