sqlms-access

SQL Learner -- MS Access 2013 ON UPDATE CASCADE and ON DELETE CASCADE

I'm new to SQL and try to get some handy knowledge from the book "SQL for Microsoft Access 2nd Edition" published in 2008.

In chapter 3, keyword ON UPDATE CASCADE ON DELETE CASCADE are introduced. I tried to run the statements with the keywords in MS Access 2013's SQL view. There is an error message saying

"Syntax error in CONSTRAINT clause."

The statements work perfectly without ON UPDATE CASCADE ON DELETE CASCADE.

The note in the book explain the keywords don't work on the version before SQL-92. I guess Access 2013 is way after SQL-92.

Can anyone explain to me why the keywords don't work?

below is the statements (ON UPDATE CASCADE ON DELETE CASCADE is at the end):

CREATE TABLE tblManufacturers
(
ManufacturerID INTEGER CONSTRAINT ManfID PRIMARY KEY,
ToyID INTEGER NOT NULL,
CompanyName CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
City CHAR (20) NOT NULL,
State CHAR (2) NOT NULL,
PostalCode CHAR (5) NOT NULL,
AreaCode CHAR (3) NOT NULL,
PhoneNumber CHAR (8) NOT NULL UNIQUE,
CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES tblToys (ToyID) 
ON UPDATE CASCADE
ON DELETE CASCADE
);
Solution

  • The ON UPDATE CASCADE and ON DELETE CASCADE statements are not supported by Access (see https://msdn.microsoft.com/en-us/library/office/ff836971.aspx).

    Regarding the functionality, you should not have a need to use ON UPDATE CASCADE. This constraint means that if you do change a primary key in the master table, the changes propagate to every child table referencing the master table. Changing a primary key is considered a no-go in the SQL world. If you really must change primary keys (because of some kind of disaster struck), this will be done with a script, lots of backup and extreme care. A primary key is this: a unique (ideally globally), immutable identifier of a row in a table.

    The ON DELETE CASCADE means that if you remove a row in the master table, any rows referencing this key in child tables will also be deleted. While this sounds like a lazy shortcut, I would recommend against it and doing this within application logic (there might be a use case where you want to retain records or log them or do something with them instead of blindly erasing them from the database.