Results 1 to 5 of 5

Thread: [RESOLVED] Can I trouble you to fact-check me?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] Can I trouble you to fact-check me?

    I want to make sure I am doing this correctly. It works in development, but I want to be sure I'll have no surprises when I release it live.

    I have a master table and a detail table.
    The key in the master is "Control" and the foreign key in the detail table to its master record is "MasterControl".
    When the user clicks a button to delete records, I want to delete the master and its details so I created a delete cascade constraint so that I can simply:
    Code:
                if (MessageBox.Show("This will delete all MR Order master and detail records loaded for this case so you can start over.", "Clear DB Records For This Case?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
                {
                    taMaster.Delete(jobControl);
                    PopulateMROrderTab(jobControl);
                }
    My question is, do I need both FK_Master_Detail_Cascade and FK_xtblMROrderMaster_xtblMROrderDetail or would the former also enforce the fk relationship in the first place, so it's redundant to have both?

    Code:
    ALTER TABLE [dbo].[xtblMROrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_Master_Detail_Cascade] FOREIGN KEY([MasterControl])
    REFERENCES [dbo].[xtblMROrderMaster] ([Control])
    ON DELETE CASCADE
    GO
    
    ALTER TABLE [dbo].[xtblMROrderDetail] CHECK CONSTRAINT [FK_Master_Detail_Cascade]
    GO
    
    ALTER TABLE [dbo].[xtblMROrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_xtblMROrderMaster_xtblMROrderDetail] FOREIGN KEY([MasterControl])
    REFERENCES [dbo].[xtblMROrderMaster] ([Control])
    GO
    
    ALTER TABLE [dbo].[xtblMROrderDetail] CHECK CONSTRAINT [FK_xtblMROrderMaster_xtblMROrderDetail]
    GO
    Also, in SSMS why aren't these listed in the tree under "Constraints"? I have to script the table to "see" them?

    Thank you.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Can I trouble you to fact-check me?

    I've checked and you're not fat. You look lovely. Oh wait... I miss-read the title.

    do I need both FK_Master_Detail_Cascade and FK_xtblMROrderMaster_xtblMROrderDetail or would the former also enforce the fk relationship in the first place, so it's redundant to have both?
    FK_xtblMROrderMaster_xtblMROrderDetail is redundant. Both statements add a foreign key and both set a behavior for how to handle an integrity constraint violation caused by a deletion. The first sets it to cascade because you've specified it. The second sets it to No Action which is the default.

    So in your case when a master record is deleted, BOTH foreign keys are processed. One deletes the children, the other does nothing. Because the first deleted the children it avoided an integrity constraint violation and the deletion is allowed to go ahead.

    Personally, though, I would remove FK_xtblMROrderMaster_xtblMROrderDetail entirely. It's not doing anything and it just creates confusion.

    Also, in SSMS why aren't these listed in the tree under "Constraints"?
    There's some confusing terminology at play here. In "Database" terms (and English, come to think of it) a foreign Key is a constraint because it constrains the behavior of the database. However, SQLServer also has a concept of a Constraint as a programmed bit of script that does some check whenever a record is inserted, updated or deleted and either allows the change to go ahead or refuses it. IE, you might refuse to allow a customer whose age is less than 30 unless they're blond and own a dog, in which case they can be 45... as long as it's a Tuesday. It is this type of constraint your seeing in the object explorer of SSMS.

    If you want to see your foreign key constraints you should see them under the "Keys" node which is immediately above the "Constraints" one.
    Last edited by FunkyDexter; Apr 24th, 2018 at 04:54 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can I trouble you to fact-check me?

    @FD - all I can say is "Thorough AF!"

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Can I trouble you to fact-check me?

    Yes! Thank you, I have deleted the FK you said personally you would remove as I felt the same way. And I'm looking at "Keys" more often!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: [RESOLVED] Can I trouble you to fact-check me?

    Yeah, it's worth spending a bit of time checking the foreign keys in your system. You're unlikely (I hope) to find too many duplicate keys but, if this system is fairly mature, you're likely to find a whole bunch that are missing.

    Knowing you've got all your integrity constraints properly in place can give you a real peace of mind that record counts etc. will match up when you come to do reporting.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Click Here to Expand Forum to Full Width