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."
ON UPDATE CASCADE ON DELETE CASCADE
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
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. Is 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.
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 form the database.