sqlmysql-workbenchentity-relationshiperdcardinality

Does a nullable foreign key always mean optional in ERD cardinality?


Suppose you have a ticket table and a status ticket. Below are their definitions:

CREATE TABLE `priorities`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` INT NOT NULL,
    constraint `priorities_id_primary` Primary key (`id`)
);

CREATE TABLE `tickets`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `subject` VARCHAR(255) NOT NULL,
    `description` Text NOT NULL,
    `created` DATETIME NOT NULL,
    `creatorId` INT UNSIGNED NOT NULL,
    `employeeId` INT UNSIGNED NULL,
    `lastModified` DATETIME NOT NULL,
    `statusId` INT UNSIGNED NULL,
    `priorityId` INT UNSIGNED NOT NULL,
    constraint `tickets_id_primary` Primary key (`id`),
    CONSTRAINT `tickets_creatorid_foreign` FOREIGN KEY(`creatorId`) REFERENCES `users`(`id`),
    CONSTRAINT `tickets_employeeid_foreign` FOREIGN KEY(`employeeId`) REFERENCES `users`(`id`),
    CONSTRAINT `tickets_statusid_foreign` FOREIGN KEY(`statusId`) REFERENCES `statuses`(`id`),
    CONSTRAINT `tickets_priorityid_foreign` FOREIGN KEY(`priorityId`) REFERENCES `priorities`(`id`)
);

As you can see, the foreign key statusId is nullable. But when I use MySQL Workbench to draw the ERD, it shows the relationship as One (and only one) but I think it should be Zero or one. Doesn't the nullable dictate that or am I mistaken? Can we have a nullable foreign key with a One (and only one) relationship?

ERD


Solution

  • The nullable foreign key corresponds definitively to an optional relationship, that should be represented with --|o- and not --||-

    This should be a bug. It may be related to the absence of ON DELETE SET NULL in your foreign key constraint (i.e. with your current constraint, it is not possible to delete the status if it is still used as a foreign key in ticket, which could be mis-interpreted as a mandatory relationship - which it is not).