mysql - 如何在父表中实现 SQL 外键

Question

第一个问题:

目前我正在从事一个需要使用 3 个不同实体的项目。这些实体是:用户、 field 和设施。

每个实体都有自己的表:

设施表:

CREATE TABLE `facility` (
`facility_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`venue_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`surface` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`max_timeblock` INT(10) UNSIGNED NOT NULL,
`min_timeblock` INT(10) UNSIGNED NOT NULL,
`availability` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`facility_id`),
UNIQUE INDEX `facility_id` (`facility_id`),
INDEX `facility-venue_id_key` (`venue_id`),
CONSTRAINT `facility-venue_id_key` FOREIGN KEY (`venue_id`) REFERENCES `venue` (`venue_id`) ON UPDATE CASCADE ON DELETE CASCADE)

field 表:

CREATE TABLE `venue` (
    `venue_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `description` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `email` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `phone` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `opening_time` TIME NOT NULL,
    `closing_time` TIME NOT NULL,
    `booking_conditions` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `booking_method` INT(10) UNSIGNED NOT NULL,
    `status` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`venue_id`),
    UNIQUE INDEX `venue_id` (`venue_id`)
)

用户表:

CREATE TABLE `user` (
    `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'This field will hold the Unique ID for each user.',
    `username` VARCHAR(50) NOT NULL COMMENT 'This field will be classed as a display name for the users so when searching they arent looking for the ID.' COLLATE 'utf8_unicode_ci',
    `email` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `password` VARCHAR(128) NOT NULL COLLATE 'utf8_unicode_ci',
    `first_name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `facebook_id` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `user_type_id` INT(10) NULL DEFAULT NULL,
    `postcode` INT(10) NULL DEFAULT NULL,
    `status` INT(10) NULL DEFAULT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    `date_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`),
    UNIQUE INDEX `username` (`username`),
    UNIQUE INDEX `email` (`email`),
    UNIQUE INDEX `user_id` (`user_id`)
)

因此，我正在尝试实现媒体表。

此表的示例如下:

CREATE TABLE `media` (
    `media_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `album_id` INT(10) UNSIGNED NULL DEFAULT '0',
    `alt_name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
    `mine_type` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
    `url_link` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`media_id`),
    INDEX `media-album_id_key` (`album_id`),
    CONSTRAINT `media-album_id_key` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`) ON UPDATE CASCADE ON DELETE CASCADE
)

我想要的是允许用户、设施关联 1 张图像，并允许场所关联相册。

因此，我不确定如何在这方面实现外键，因为如果我将一列媒体放入用户表并为 Media_ID 设置一个外键，如果我删除媒体，它会删除用户，因为级联影响这样做。

我想要的是，如果我删除用户/设施/地点，它将删除与其关联的所有图像。

如有任何帮助，我们将不胜感激。

问候，

罗伯特

Answer 1

如果不使用触发器，您无法直接做您想做的事情，因此我在此处将其列为您的第一个选项，以及您确实应该避免但会为您提供有关如何调查此问题的第二个选项的第二个选项模式问题更进一步。

• 将每个设施/地点/用户的外键保存到 media 表，但使用 ON DELETE SET NULL 而不是 ON DELETE CASCADE referential action，然后在你的应用代码中(或者触发器中)实现相关媒体的删除
• 查找“多态关联”以及为什么它们在关系数据库中不是一个好主意，如果您仍然确信自己想要这样做，请使用一种可用的技术来构建您正在考虑的倒置关系做。