composite key where one field is cascade delete and the other is cascade set null

[NotionCommotion]

I have the following three tables.  If network is deleted, network_node should not be deleted but remain an orphan which potentially will be added back.  Is there any way to enforce by the database that network_node.network_id and network_node_account_id are both associated with the same account?  I tried creating a forth table which had a composite key network_id and account_id where network_id was cascade set null, and then had network_node reference that table, but I get errno: 150 "Foreign key constraint is incorrectly formed".  Is this not possible?  Thanks

• account
  • id
  • name
• network
  • id
  • account_id (FK on delete cascade)
  • name
• network_node
  • id
  • network_id (FK on delete set null)
  • account_id (FK on delete cascade)

[requinix]

"Added back"? That doesn't sound right. The network ID should be artificial, so if you "add back" the network then it will have a new ID.

So either,
a) The network ID is not artificial. It should be some unique identifier inherent to the network. I have no idea what that might be, but it would be something you can recover when you restore the deleted network.
b) Don't delete networks. Mark them as deleted but leave the data intact. Then you can unmark it later.

[NotionCommotion]

2 hours ago, requinix said:

"Added back"? That doesn't sound right. The network ID should be artificial, so if you "add back" the network then it will have a new ID.

So either,
a) The network ID is not artificial. It should be some unique identifier inherent to the network. I have no idea what that might be, but it would be something you can recover when you restore the deleted network.
b) Don't delete networks. Mark them as deleted but leave the data intact. Then you can unmark it later.

Thanks requinix,

a) The networks cannot be added back, but only the network_nodes (and other records linked only to the network_nodes).  Each account can have many networks and many network_nodes, and maybe I should have named "network" as "virtual_network" and "network_node" as "computer".  The network ID is an auto-increment surrogate, and if a given network is deleted, then it is gone for good, but not the network_nodes, and network_node.network_id will cascade to NULL.  The network_nodes, however, must still be tied to the account so that the application can allow a user to view only those network_nodes for their account (via network_node.account_id) which are not tied to a given network (i.e. network_node.network_id IS NULL), and add them back to another existing network should they desire.

b) I was originally considering doing so, but it adds complexity due to requirements which I didn't initially provide.  For a given account, network.name must be unique so I will need to add some "deleted_name" field and move the name to this field.  This seems easy enough, and while I've haven't heard of others doing this approach, expect it is a pretty standard approach, agree?  What is a bigger problem is other tables have a unique constraint on network_id and a user defined natural key.  Given this requirement, recommend not using this approach?

Maybe I will need to just use the applicable to ensure that network and network_node are both tied to a common account, but it is always nice to use the database if reasonable simple to do.

Thanks

Edited October 31, 2019 by NotionCommotion

[requinix]

Oh, so the node's network_id is not so much "child of" but more "assigned to". That's fine.

MySQL's foreign keys are always MATCH SIMPLE, meaning that any value can be NULL and the key just won't be enforced. So give the node a foreign key (network_id, account_id) to network's (id, account_id).

[NotionCommotion]

Maybe I misunderstand.

I knew that unique constraint of one or more column where at least one column value is NULL is never considered a duplicate because NULL is, well, NULL.

But I was still getting the error: errno: 150 "Foreign key constraint is incorrectly formed"

So, I tried using MySQL Workbench to create the same structure.  And then I went to make the foreign key on network_node ON DELETE CASCADE for network_id to ON DELETE SET NULL for account_id, and as seen below it did not give me the option to do so.  Maybe not possible?

Not the (maybe only) cause of the error, however, and I am still getting this weird "Incorrect options in FOREIGN KEY" error when changing a presumably valid ON DELETE from CASCADE to SET NULL.  Note sure, but think it has something to do with a given foreign constraint in one table conflicting with a foreign constraint in another table.  Have you come across this "Incorrect options..." error before?

-- MySQL Script generated by MySQL Workbench
-- Thu Oct 31 15:29:53 2019
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`account` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`network`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`network` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `account_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  INDEX `fk_network_account_idx` (`account_id` ASC),
  CONSTRAINT `fk_network_account`
    FOREIGN KEY (`account_id`)
    REFERENCES `mydb`.`account` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`network_has_account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`network_has_account` (
  `network_id` INT NULL,
  `account_id` INT NOT NULL,
  PRIMARY KEY (`network_id`, `account_id`),
  INDEX `fk_network_has_account_account1_idx` (`account_id` ASC),
  INDEX `fk_network_has_account_network1_idx` (`network_id` ASC),
  CONSTRAINT `fk_network_has_account_network1`
    FOREIGN KEY (`network_id`)
    REFERENCES `mydb`.`network` (`id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_network_has_account_account1`
    FOREIGN KEY (`account_id`)
    REFERENCES `mydb`.`account` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`network_node`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`network_node` (
  `id` INT NOT NULL,
  `network_id` INT NOT NULL,
  `account_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_network_node_type2_network_has_account1_idx` (`network_id` ASC, `account_id` ASC),
  CONSTRAINT `fk_network_node_network_has_account1`
    FOREIGN KEY (`network_id` , `account_id`)
    REFERENCES `mydb`.`network_has_account` (`network_id` , `account_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

 

Edited November 1, 2019 by requinix
removed the extra images

[NotionCommotion]

PS.  Please disregard all those stupid duplicated ERDs at the bottom of my last post.  Thought they were deleted but evidently weren't.

[requinix]

I don't mean to replace your network_id key with this. Keep it with its CASCADE SET NULL. And the account_id key. Add a new key, with no cascading options, that is specifically about enforcing the account_id agrees with the (if present) network_id.

6 hours ago, NotionCommotion said:

PS.  Please disregard all those stupid duplicated ERDs at the bottom of my last post.  Thought they were deleted but evidently weren't.

You have to delete the attachment too, otherwise IPB will think you want them added automatically.

[NotionCommotion]

6 hours ago, requinix said:

I don't mean to replace your network_id key with this. Keep it with its CASCADE SET NULL. And the account_id key. Add a new key, with no cascading options, that is specifically about enforcing the account_id agrees with the (if present) network_id.

You just taught me something.  I never realized foreign keys could be used on a given column to multiple tables.  I also never realized that ON DELETE and ON UPDATE can not be defined (PS.  while it is true, reverse engineering into MySQL Workbench displays it as RESTRICT, but won't really change it to RESTRICT).  Thanks!!!

Do you mean something like the create statement at the bottom?  How will this work regarding network_has_account?  If a given network was deleted, then network_has_account would have NULL for one column of the composite primary key which surely isn't valid (and just to be sure, I tried doing so and of course couldn't even create the table).  I guess I can add a surrogate PK instead of the composite PK but that doesn't make sense....

Oh...  You really mean get rid of network_has_account have a FK to network.id and network.account_id.  Humm, that won't work because account_id isn't part of network's PK.  Maybe I make account_id as part of network's PK?  Oh, then I am back where I started where I am trying to make network's composite PK allow NULL in one of its columns...

While I am glad I learned more about foreign constraints, maybe I am going down an XY rabbit hole.  Let me start over with high level requirements:

1. network has one account, and account can have zero or many networks.  If an account is deleted, all its networks should be deleted.
2. network_node has zero or one network, and network can have zero or many network_nodes.  Note that when adding a new network_node, the application will always ensure that it has a network, so I guess I should have said "network can have one or many network_nodes", but maybe the DB doesn't need to enforce.  A network_node's network can be changed but only to another network belonging to the same account.
3. I must be able to identify all network_nodes regardless of whether they have a network on a per account basis, and if the account is deleted, all associated network_nodes should be deleted.

How would you implement this?  Thank you

EDIT.  Oh, just put a unique constraint on network id/account_id so then I can have a FK from network_node network_id/account_id to network id/account_id?

CREATE TABLE IF NOT EXISTS `mydb`.`network_node` (
  `id` INT NOT NULL,
  `network_id` INT NULL,
  `account_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_network_node_type2_network_has_account1_idx` (`network_id` ASC, `account_id` ASC),
  INDEX `fk_account_only_idx` (`account_id` ASC),
  CONSTRAINT `fk_account_network_match`
    FOREIGN KEY (`network_id` , `account_id`)
    REFERENCES `mydb`.`network_has_account` (`network_id` , `account_id`),
  CONSTRAINT `fk_network_only`
    FOREIGN KEY (`network_id`)
    REFERENCES `mydb`.`network` (`id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_only`
    FOREIGN KEY (`account_id`)
    REFERENCES `mydb`.`account` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

 

Edited November 1, 2019 by NotionCommotion

[NotionCommotion]

Thanks again,  The following works perfect.  If you feel I should be doing differently, please advise, and otherwise I am good to go.

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB;

CREATE TABLE `network` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`name`,`account_id`),
  UNIQUE KEY `unique_pk` (`id`,`account_id`),
  KEY `fk_network_account_idx` (`account_id`),
  CONSTRAINT `fk_network_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;

CREATE TABLE `network_node` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `network_id` int(11) DEFAULT NULL,
  `account_id` int(11) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_network_node_network1_idx` (`network_id`),
  KEY `fk_network_node_account1_idx` (`account_id`),
  KEY `fk_shared_idx` (`network_id`,`account_id`),
  UNIQUE KEY `unique_name` (`network_id`, `name`),
  CONSTRAINT `fk_network_node_account1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_network_node_network1` FOREIGN KEY (`network_id`) REFERENCES `network` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `fk_shared` FOREIGN KEY (`network_id`, `account_id`) REFERENCES `network` (`id`, `account_id`)
) ENGINE=InnoDB;

 

[kicken]

If everything is working for you, then great, run with it.  However, I'm curious if there's a particular reason you cant just add/remove your network_node entries as needed?

You say every account has a specific number of them.  That seems like a requirement best handled at the software level where these records are managed.  In the database you'd just have nodes that are actually in use, but in the management UI you could display X number of nodes for the account at all times.  If the user adds a network to the node, insert a new network_node.  If they remove the network, delete the node.

 

 

[NotionCommotion]

1 hour ago, kicken said:

If everything is working for you, then great, run with it.  However, I'm curious if there's a particular reason you cant just add/remove your network_node entries as needed?

You say every account has a specific number of them.  That seems like a requirement best handled at the software level where these records are managed.  In the database you'd just have nodes that are actually in use, but in the management UI you could display X number of nodes for the account at all times.  If the user adds a network to the node, insert a new network_node.  If they remove the network, delete the node.

The network_node could also be uniquely identified by a composite natural key, however, I've finally came to the conclusion that big old composite keys usually don't end well and elected to use a surrogate.  Furthermore, network_node.id is being used by another non-SQL application as a natural key.  If a network is deleted or a network_node is tagged as deleted, and then later a network_node is added with  that composite natural key I described, then this network_node needs to utilize the same PK so it may be recognized by the non-SQL application.  Maybe way to complicated and a better way..