May 26, 2008 at 10:49 am
Is using ON DELETE CASCADE always preferred versus creating stored procedures to perform the same functionality?
Here's a couple of sample tables for my scenario (MySQL script source code):
CREATE TABLE invoice (invoice_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
special_request_detail VARCHAR(1000) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE invoice_status (invoice_id INT(10) NOT NULL,
status_id INT(10) NOT NULL,
PRIMARY KEY (invoice_id, status_id),
FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id) ON DELETE CASCADE,
FOREIGN KEY (status_id) REFERENCES status (status_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE status (status_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
status_summary VARCHAR(100) NOT NULL,
status_detail VARCHAR(1000) NOT NULL,
status_date_time DATETIME NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE receives (customer_id INT(10) NOT NULL,
invoice_id INT(10) NOT NULL,
PRIMARY KEY (customer_id, invoice_id),
FOREIGN KEY (customer_id) REFERENCES customer (customer_id),
FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE customer (customer_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB DEFAULT CHARSET=utf8;
If I delete an invoice.invoice_id, I want to delete the corresponding invoice_status and receives table entries. Additionally, I want to delete the status table entry that corresponds to invoice_status b/c the status entry would be orphaned upon invoice_status table entry deletion.
If I used ON DELETE CASCADE within a stored procedure, I would delete invoice.invoice_id and then the corresponding status table entry. However, a malicious user might try to delete an invoice_id directly, and the corresponding status table entry would still be orphaned.
If I don't use ON DELETE CASCADE, I would create a stored procedure to delete invoice_status, receives, invoice, and status table entries manually (i.e., separate delete statements). If a malicious user tried to delete invoice_id directly, they wouldn't be able to b/c of foreign key constraints (receives and invoice_status tables).
Thanks in advance for your help,
Jon
May 26, 2008 at 12:03 pm
jlp3630 (5/26/2008)
Is using ON DELETE CASCADE always preferred versus creating stored procedures to perform the same functionality?
Definitely not. In fact I would say that it is more the reverse: I would only ever use cascading deletes under exceptional circumstances. IMHO stored procedures should always be regarded as the de facto required way to access and manage tables; all other approaches must justify themselves over stored procedures.
If I used ON DELETE CASCADE within a stored procedure, I would delete invoice.invoice_id and then the corresponding status table entry. However, a malicious user might try to delete an invoice_id directly, and the corresponding status table entry would still be orphaned.
The way to deal with the threat of malicious users is to NOT give them access to the tables in the first place. Instead only give users access to the stored procedures and then give the stored procedures access to the tables.
If I don't use ON DELETE CASCADE, I would create a stored procedure to delete invoice_status, receives, invoice, and status table entries manually (i.e., separate delete statements). If a malicious user tried to delete invoice_id directly, they wouldn't be able to b/c of foreign key constraints (receives and invoice_status tables).
Foriegn Keys are a Data Integrity feature, NOT a Security feature and they should not be relied on for security. Use your security features for security, as I have outlined above.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 5:44 am
Wouldn't using stored procedures add another step in the deletion process? If foreign key constraints are already checked, wouldn't it be faster to just do the deletion at that time?
Or is it just bad programming practice to have "ON DELETE CASCADE"? Would it cause "unintended" side effects to unsuspecting programmers and undue complexity?
May 27, 2008 at 6:27 am
I tend to put cascading deletes into the same bucket as deferred constraints (in Oracle), the NOLOCK query hint, and most triggers. They are there to be used in instances in which you are somewhat left without another choice.
I think of them as anti-data integrity. Rather than your foreign key constraint being there to ensure you do not delete a parent record that has children, your constraint is now there to ensure all children are deleted with their parents. Yes, this prevents orphaned records, but I like to think foreign key constraints are more to prevent accidental delete more than they are there to ensure complete deletion.
If you are building an application from scratch, it is best to have your delete operations happen in the correct order rather than having something that is going to mysteriously delete data "automatically" because it thinks it is data you do not want.
May 27, 2008 at 8:30 am
jlp3630 (5/27/2008)
Or is it just bad programming practice to have "ON DELETE CASCADE"? Would it cause "unintended" side effects to unsuspecting programmers and undue complexity?
In my opinion, yes, it is bad programming practice.
The problem is that it confuses Functional issues (which are the developers realm) with Operational issues (which are the DBA's realm). And I agree with Michael Earl, they should only be used as a last resort.
Deleting related records (and all data manipulation, for that matter) is an application functional feature and should be written in a straight-forward and obvious way that is out front and right were any maintenance programmer would expect it to be. Not hidden behind the scenes in a Referential Integrity check.
As for performance, I doubt that there is 5 cents difference between them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply