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?
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).