Post reply

A constaint Question about a Multiple part key

  • August 25, 2008 at 7:11 am

    #382397

    I have a parent table that has a primary Key that is a composite of 3 fields:

    PartNumber (varchar 12), YYYYMMDD (char 8) and SeqNo (Char 3).

    I have a child table that has a field called:

    SalesItem (VarChar 23)

    I want to setup a constraint in the child table so that SalesItem is really a foreign key to PartNumber, YYYYMMDD and SeqNo.

    Can this be done? If so, how? I would prefer that SalesItem field in the child table not be broken down into its component pieces because I don't see how I can guarantee that Part Number XYZ is constained against the correct YYYYMMDD and SeqNo.

    Thanks

  • August 25, 2008 at 9:12 am

    #862581

    Maybe I'm not understanding the question, but it seems to me that breaking up the SalesItem field into PartNumber, YYYYMMDD, SeqNo would be the best answer. You can have a multi-column foreign key just as easily as you can have a multi-column primary key. Is there another reason not to break up SalesItem?

  • August 25, 2008 at 11:38 am

    #862685

    I am fairly new at this DB stuff, so maybe what I am about to state makes no sense. So please bear with me.

    My concern with breaking SalesItem up into the component fields was for the following reason:

    Suppose I have two rows in the table:

    Part Number YYYYMMDD SeqNo

    ABCDEFG 20080615 01

    ZZZZZZZ 200081012 01

    If I break up the salesItem into is component parts and put a constraint on each of the fields, couldn't someone possibly enter a SalesItem in the child table as:

    Part Number YYYYMMDD SeqNo

    ZZZZZZZ 20080615 01

    Wouldn't this type of entry be considered valid even though there is no entry in the parent table that matches this Part Number, YYYYMMDD and SeqNo combination? Doesn't each individual field pass the constaint?

    Thanks

  • August 25, 2008 at 12:49 pm

    #862726

    What I'd suggest, is that your primary key on the parent table looks something like this:

    [font="Courier New"]CONSTRAINT PK_ParentTable PRIMARY KEY(PartNumber, YYYYMMDD, SeqNo)[/font]

    and that the foregn key on the child table looks something like this:

    [font="Courier New"]CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY(PartNumber, YYYYMMDD, SeqNo) REFERENCES ParentTable[/font]

    so it would prevent the situation you described from being a valid record in the child table.

  • August 26, 2008 at 7:04 am

    #863093

    That did the trick.

    Thanks

  • September 2, 2008 at 11:04 am

    #866095

    meichner (8/25/2008)

    I am fairly new at this DB stuff...

    Yes, that you are new was obvious from the fact that all your fields are strings, even though some contain dates and some contain numeric data.

    You also have too many fields in your "key." The part number and date are sufficient to uniquely identity each part... or the part number and sequence. A sensible design would be to have the part number and sequence as the PK and the date field to track when each particular sequence was created. The date is information you may want to track, but it's not part of the key.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

Viewing 6 posts - 1 through 5 (of 5 total)

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