MySQL/MariaDB FOREIGN KEYs and DELETE ON CASCADE

Development Code
1

I just want to make sure I understand the documentation and examples I read online correctly in making my little database.
My goal is to have rows in the the switchrolevlan table delete when a row in the switch table is deleted that is referenced by the switchrolevlan row (same goes for the node table and the noderoleassign table). I’m worried that I may have the SQL code backwards. Also, I have never made use of FORIEGN KEY before, so I may have the syntax regarding that incorrect as well.

CREATE DATABASE pffailover;
USE DATABASE pffailover;

CREATE TABLE role(
   role_id SMALLINT NOT NULL UNIQUE PRIMARY KEY,
   name TINYTEXT NOT NULL,
   notes TEXT,
);
CREATE TABLE switch(
    switch_id varchar(50) NOT NULL UNIQUE PRIMARY KEY,
    type TINYTEXT,
    description TEXT,
);

    CREATE TABLE switchrolevlan(
        switch_id varchar(50) NOT NULL FOREIGN KEY,
        role_id SMALLINT NOT NULL,
        vlanNum SMALLINT NOT NULL,
        FOREIGN KEY (role_id)
            REFERENCES role (role_id)
            ON DELETE CASCADE
        FOREIGN KEY (switch_id)
            REFERENCES switch (switch_id)
            ON DELETE CASCADE
    );

    CREATE TABLE node(
        mac varchar(18) NOT NULL PRIMARY KEY,
        reg BOOLEAN NOT NULL DEFAULT FALSE,
        voip BOOLEAN NOT NULL DEFAULT FALSE,
    );

    CREATE TABLE noderoleassign(
        mac varchar(18) NOT NULL FOREIGN KEY,
        role_id SMALLINT NOT NULL FOREIGN KEY,
        FOREIGN KEY (mac)
            REFERENCES node (mac)
            ON DELETE CASCADE
        FOREIGN KEY (role_id)
            REFERENCES role (role_id)
            ON DELETE CASCADE
    );
2

Looks fine to me. The only thing that stands out is I don’t think you need to have a unique call on a primary key field.

Did you test it and find it didn’t work?

1 Like
3

I cleaned up the syntax a bit, but MySQL throws errors when I attempt to run it:

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘)’ at line 5

Here is my adjusted code:

CREATE DATABASE pffailover;
USE pffailover;

CREATE TABLE role (
    role_id SMALLINT NOT NULL PRIMARY KEY,
    name TINYTEXT NOT NULL,
    notes TEXT,
);

CREATE TABLE switch (
    switch_id varchar(50) NOT NULL PRIMARY KEY,
    type TINYTEXT,
    description TEXT,
);

CREATE TABLE switchrolevlan (
    switch_id varchar(50) NOT NULL FOREIGN KEY REFERENCES switch (switch_id) ON DELETE CASCADE,
    role_id SMALLINT NOT NULL FOREIGN KEY REFERENCES role (role_id) ON DELETE CASCADE,
    vlanNum SMALLINT NOT NULL
);

CREATE TABLE node (
    mac varchar(18) NOT NULL PRIMARY KEY,
    reg BOOLEAN NOT NULL DEFAULT FALSE,
    voip BOOLEAN NOT NULL DEFAULT FALSE,
);

CREATE TABLE noderoleassign (
    mac varchar(18) NOT NULL FOREIGN KEY REFERENCES node (mac) ON DELETE CASCADE,
    role_id SMALLINT NOT NULL FOREIGN KEY REFERENCES role (role_id) ON DELETE CASCADE
);
4

Figured it out. I had a comma on that last line :frowning:

2 Likes
5

Also figured out that using FOREIGN KEY inline with the column didn’t work, so I put it back to a separate line.