-
Apr 20th, 2018, 09:55 AM
#1
Thread Starter
PowerPoster
[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.
-
Apr 24th, 2018, 04:49 AM
#2
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
-
Apr 24th, 2018, 02:54 PM
#3
Re: Can I trouble you to fact-check me?
@FD - all I can say is "Thorough AF!"
-
May 1st, 2018, 12:55 PM
#4
Thread Starter
PowerPoster
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.
-
May 2nd, 2018, 02:37 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|