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
);