Issue with cascading

Question

I am a student currently learning SQL coding and for some reason I can't seem to get this error.

CREATE TABLE CUSTOMER
(
    id           CHAR(9)      NOT NULL,
    CustomerName CHAR(50)     NOT NULL,
    Address      VARCHAR(100) NOT NULL,
    Contact      CHAR(8)      NOT NULL,
    DateofBirth  DATE         NOT NULL,
    Occupation   VARCHAR(30)  NOT NULL,

    CONSTRAINT CUSTOMER_PK  
        PRIMARY KEY(id),
        ON UPDATE CASCADE
        ON DELETE CASCADE  -- default if not specify
    CONSTRAINT CHECK (ID LIKE '[ST][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')
    CONSTRAINT CHECK (YEAR(GETDATE()) - YEAR(dateofBirth) > 21)
);

I get this error:

Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'ON'

Tip: Don't name columns `Id` because then their names become ambiguous. Also, why `char(9)` instead of `int IDENTITY`? — Dai, Sep 03 '21 at 04:05
To @Dai's point, if you name the primary key `CustomerId` then you can often use natural joints so you don't have have to express the joint condition explicitly. It leads to more readable queries. — Allan Wind, Sep 03 '21 at 04:10
`CustomerName CHAR(50)` seems inappropriate - `CHAR` will be padding the value you store into it with spaces, to the right, to the defined length (50 chars). This makes really no sense with a customer name. Use `VARCHAR(50)` instead - stores only what you've entered, no padding, no unnecessary spaces being stored for eternity.... — marc_s, Sep 03 '21 at 04:35

Dai · Answer 1 · 2021-09-03T05:49:23.600

The ON UPDATE CASCADE and ON DELETE CASCADE options apply to foreign keys, not primary-keys.
- So you specify that option in your other tables which reference CUSTOMER, not in the CUSTOMER table itself.
- That said, primary-keys should be immutable (i.e. they never change) so if you find yourself needing to use ON UPDATE CASCADE then there's probably something wrong with your database design.
- However, using ON DELETE CASCADE is fine, however. (Just make sure you have backups so you don't accidentally nuke your entire database when you delete 1 row in a complex object-graph where the entire thing cascades...)
Also, this is wrong:

CONSTRAINT CHECK (YEAR(GETDATE()) - YEAR(dateofBirth) > 21)

I assume this is meant to ensure the customer's "Age" is over 21 - however what it actually does it check if there's a difference of 22 (not 21) years between the two dates, which won't work if someone is born 2000-12-31 and today is 2022-01-01, for example.

Also, you should name your constraints so you know what it's meant to do.

Instead, use this technique for calculating an age from two dates.

_I assume this is meant to ensure the customer's "Age" is over 21_ Presumably you meant to write "age is at least 21" or "age is 21 or older". Writing requirements is difficult, even when you know what is desired. — SMor, Sep 03 '21 at 11:56

Damith Benaragama · Answer 2 · 2021-09-03T06:18:40.377

There should be a relationship to another (foreign) table to use ON DELETE CASCADE or ON UPDATE CASCADE.

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

In your case, it seems that there is no relationship with any other table. So, you do not need these two constraints.

Further, you cannot use a Function or Expression in the CHECK clause. Therefore, you have to exclude Age validation too. You can implement it using an insert/update trigger.

CREATE TABLE CUSTOMER(
    id                  Char(9)         NOT NULL,
    CustomerName        CHAR(50)        NOT NULL,
    Address             VARCHAR(100)    NOT NULL,
    Contact             CHAR(8)         NOT NULL,
    DateofBirth         DATE            NOT NULL,
    Occupation          VARCHAR(30)     NOT NULL,
    CONSTRAINT          CUSTOMER_PK PRIMARY KEY(id),
    --                            ON UPDATE CASCADE
    --                            ON DELETE CASCADE  -- default if not specify
    CONSTRAINT          CHECK (ID LIKE '[ST][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')
    -- CONSTRAINT          CHECK (YEAR(GETDATE()) - YEAR(dateofBirth) > 21));

FOREIGN KEY Constraints MySQL reference

FOREIGN KEY Constraints w3schools reference

CHECK Constraints MySQL reference

CHECK Constraints w3schools reference

MySQL ON DELETE CASCADE

PS: if you are using SQL server you can change your SQL as follows

CREATE TABLE CUSTOMER(
id                  Char(9)         NOT NULL,
CustomerName        CHAR(50)        NOT NULL,
Address             VARCHAR(100)    NOT NULL,
Contact             CHAR(8)         NOT NULL,
DateofBirth         DATE            NOT NULL,
Occupation          VARCHAR(30)     NOT NULL,
CONSTRAINT          CUSTOMER_PK PRIMARY KEY(id),
--                            ON UPDATE CASCADE
--                            ON DELETE CASCADE  -- default if not specify
CONSTRAINT    CHK_ID      CHECK (ID LIKE '[ST][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'),
CONSTRAINT    CKK_AGE     CHECK (YEAR(GETDATE()) - YEAR(dateofBirth) > 21)   );

"Further, you cannot use a Function or Expression in the CHECK clause." - this is incorrect: a `CHECK CONSTRAINT` **is an expression**. As for using functions, that depends on your DBMS, but SQL Server supports UDFs in `CHECK` constraints. MySQL [only supports built-in deterministic functions](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html) in `CHECK` constraints). — Dai, Sep 03 '21 at 05:51
@Dai Agreed. I checked on MySQL only. However, CHECK CONSTRAINT should have a name in SQL Server right? — Damith Benaragama, Sep 03 '21 at 06:17
Correct, SQL Server requires named constraints - though if you're using SSMS it can generate implicit constraint names for you (like it does for `DEFAULT` constraints set in the table-designer). — Dai, Sep 03 '21 at 06:27
@jarlh W3Schools has improved a _lot_ since the days when it was full of SQL-injectable PHP sample code - though I agree that people shouldn't link to W3Schools - but not because of its reputation, but simply because _it isn't authoritative_. Anything on W3Schools - _or any other "tutorial" site for that matter_ is just second-hand regurgitated information from original sources like W3.org/WHATWG (for the _actual_ HTML+CSS specs, the ECMAScript spec (for JS), or browser vendor sites like MDN (Mozilla/Firefox), MSDN/docs.ms (Windows/Edge/IE), and Google's `.dev` sites. — Dai, Sep 03 '21 at 09:09
@Dai, took a short look at it now, and I'm still far from recommending it. — jarlh, Sep 03 '21 at 09:19

score 0 · Answer 3 · answered Sep 03 '21 at 12:04

You are missing a simple point: A cascading constraint is on a foreign key reference not on a primary key.

The reason is simple: You might not want all deletions/updates to cascade. Or you might want them to cascade in different ways.

So, your syntax simply does not belong in the customers table.

If you had another table, you might have:

create table other (
    . . .,
    customerId int,
    foreign key (customerId) references customers(id)
        on delete cascade    -- or whatever
);

Issue with cascading

3 Answers3