Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2008
    Posts
    33

    Unanswered: Referencial Integrity question

    Hi,

    Let's say you have these 3 tables

    --------------------------------------------------------------

    use tempdb
    go

    create table TableMain (
    RowID int primary key identity(1, 1)
    , ItemTypeID tinyint NOT NULL
    , Details varchar(200) NOT NULL
    )
    go

    create table TableDetails (
    DetailRowID int primary key identity(1, 1)
    , RowID int NOT NULL
    , ItemTypeID tinyint NOT NULL
    , Details varchar(200) NOT NULL

    )
    go

    create table ItemTypes (
    ItemTypeID tinyint primary key identity(1, 1)
    , Details varchar(50) NOT NULL
    )
    go

    create unique nonclustered index IX_TableMain__ItemTypeID__RowID on TableMain (ItemTypeID, RowID)
    go

    alter table TableDetails
    add constraint FK_TableDetails_TableMain__RowID__ItemTypeID foreign key (ItemTypeID, RowID) references TableMain(ItemTypeID, RowID);
    go

    alter table TableMain
    add constraint FK_TableMain_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID);
    go

    /*
    drop table TableDetails
    drop table TableMain
    drop table ItemTypes
    */

    --------------------------------------------------------------

    As you can see TableDetails references TableMain by ItemTypeID and RowID. TableMain also has a foreign key on ItemTypeID.

    In this example although there is no references between TableDetails and ItemTypes on ItemTypeID field, referential integrity is still maintain because of the foreign key on TableMain (ItemTypeID, RowID)


    So here is my question. Although referential integrity is maintain with this structure, would you guys still create a foreign key on TableDetails (ItemTypeID), i.e.:

    alter table TableDetails
    add constraint FK_TableDetails_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID);
    go




    There might not be any right or wrong, it might be a personal preference sort of thing. To me it seems performance wise it's better to not create this extra contraints since it doesn't add any additional integrity, on the other hand when looking at a DB schema this extra constraint might help understanding what's going on. Also perhaps it helps SQL Server in picking the right execution plan but that I am not sure.

    Perhaps the solution is to create the constraint with a NOCHECK on it... is it?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by harveysburger
    Hi,
    would you guys still create a foreign key on TableDetails (ItemTypeID), i.e.:
    Yes I would.

    You could still orphan data. The question you need to ask yourself is "Is ItemTypeID an attribute of the details or the main record or both?". If it belongs in one, remove it from the other.

    However, let me warn you that composite keys can become a headache. I physically twinged when I saw it. Play around with some outer joins and composite keys to see what I mean. Why not use RowID as yout foriegn key TableDetails

    Quote Originally Posted by harveysburger
    with a NOCHECK on it... is it?
    Why?

    Code:
    alter table TableDetails
    add constraint FK_TableDetails_TableMain__RowID__ItemTypeID foreign key (ItemTypeID, RowID) references TableMain(ItemTypeID, RowID);
    go
    Does this even execute against a single key primary key in the Main table? I do not have SQL Server in front of me at the 2nd but I would guess this fails.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2008
    Posts
    33
    this executes fine because of the unique constraints on those fields, otherwise it would fail:

    alter table TableDetails
    add constraint FK_TableDetails_TableMain__RowID__ItemTypeID foreign key (ItemTypeID, RowID) references TableMain(ItemTypeID, RowID);
    go

  4. #4
    Join Date
    Mar 2008
    Posts
    33
    As for why the foreign isn't only RowID instead of ItemTypeID + RowID, see this thread: http://www.sqlservercentral.com/Foru...308-361-1.aspx

    basically the details for my entries are in different tables depending on the Type column

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i do not remember my login to sqlservercentral.

    basically the details for my entries are in different tables depending on the Type column
    so and why?

    do all of these tables have the same structure?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by harveysburger
    So here is my question. Although referential integrity is maintain with this structure, would you guys still create a foreign key on TableDetails (ItemTypeID
    no, i would not

    that'd be silly ™

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    no, i would not

    that'd be silly ™

    if you are trolling, it would help if you winkied and not smilied.

    he still has not answered my original question about his design.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, that was not trolling at all

    i would not declare that FK because there is no need to

    go ahead and try to enter an ItemTypeID value into TableDetails that doesn't exist in ItemTypes

    please do report back on your experience

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah I got that but that's because of that icky composite key nonsense he still has not justified.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Mar 2008
    Posts
    33
    the composite key nonsense -> Please read the thread from sqlservercentral (http://www.sqlservercentral.com/Foru....aspx?Update=1) to see the rational behind it.

    do all of these tables have the same structure? -> No, all the 'details' tables have entirely different structure. Basically the main table contains the columns common for all types of transactions, then depending on the typeID the additional details are stored in a detail table.

    as for this one: "Is ItemTypeID an attribute of the details or the main record or both?"

    ItemTypeID is an attribute of the main record. But the idea of also having the column in the details table is to create the foreign key. See the thread on sqlservercentral for the what and whys


    And here I am not advocating that what I am considering on doing here is the absolute way to go or anything like that. I am just debating few design ideas. You really have to read that thread on sqlservercentral to see where I am coming from.

  11. #11
    Join Date
    Mar 2008
    Posts
    33
    oh also, about "the composite key nonsense"

    I am not defining composite primary keys here, the PK is only made of 1 int column. It's the FK that is based on 2 columns (which is made possible by having a unique constraint on the PK and the other column..)

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    a key is a key wether it be foriegn or primary.

    I am sorry I can not read that other forum because they require a login I have long forgotten to view their threads.

    If you want to play with composite keys go right ahead. your trouble, not mine. yes there is no need for a FK unless the Main and the Details tables can have different Types, but that is not possible with your dual key which begs the question (drum roll please.....)

    Why are you storing the same data twice (the type that us)?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Mar 2008
    Posts
    33
    Why are you storing the same data twice (the type that us)?
    -> To enforce that only the typeID that are relevant to the details table can actually be stored there. So for example if a detail table is meant to be used only for TranTypeID 1, having this foreign key will make it possible to guarantee that only TranTypeID 1 can possibly have an entry in the details table.

    here is the initial post I made on sqlservercentral,

  14. #14
    Join Date
    Mar 2008
    Posts
    33
    Hi there,

    Little background. I need to design a set of tables that will hold transactions. There are different types of transactions and although about half of the columns are common for all types the other half is totally different depending on which kind of transaction we are talking about.

    Pretty simple and common scenario but for sake of having an example to work with in our discussion let's write a random case scenario:

    Columns:
    columns common to all transaction types: TranID, CustomerID, Amount, Date
    additional columns for trantype 1: ReferredCustomerID
    additional columns for trantype 2: SourceProductID, ReversedDate

    Possible Implementations:
    Scenario #1: Create one distinct table for each transaction type and create a view that provides a 'union all' of all the common columns across all the tables. The primary key on all these tables would be the TranID.

    Scenario #2: Create one global table that has all the shared columns along with a TranTypeID. When you need to query all information for a specific TranType then you need to join with whatever table contains the extra info for that TranType. The primary key on all these tables would be the TranID.

    Scenario #3: Same as Scenario #2 except that the primary key on all the tables would be TranID+TranTypeID. The table containing the additional columns for TranType #1 would always have a "1" in the TranType column. And the table containing the additional columns for TranType #2 would always have a "2" in the TranType column...

    In the past I've pretty much always followed "scenario #2" when faced with this type of scenario. However this type I am debating if I should go with #3... Going with #3 would enforce stronger integrity of my data because it guarantees that you will never have a case where a transaction of TranType 2 would end up having an entry in the details table for TranType 1... On the other hand scenario #3 uses more disk space and the overhead of maintaining the FK will be higher...


    I hope I explained myself well. As you can see it's a very simple situation but I would be curious knowing what you guys' opinion is on the matter

  15. #15
    Join Date
    Mar 2008
    Posts
    33
    everybody was strongly in favor of "scenario #3", if you think it's really not a good route to take please create an account on that site and post your point of view

    thanks for your input so far anyway, I am not set in any particular design right now, just juggling with ideas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •