如何为Songs表的AlbumID字段设置条件允许Null值？

需求说明 #

• 现有SongsInfo表（原描述中名为Songs），包含AlbumID字段（作为外键关联AlbumInfo表）和status字段（仅允许取值Released或Unreleased）
• 当前AlbumID设置为NOT NULL，需调整为：当status为Released时，AlbumID必须非空；当status为Unreleased时，AlbumID允许为空

当前表结构 #

SongID int(11) NOT NULL AUTO_INCREMENT,
SongName varchar(100) NOT NULL,
AlbumID int(11) NOT NULL,
ArtistID int(11) NOT NULL,
GenreID int(11) NOT NULL,
PRIMARY KEY (SongID),
FOREIGN KEY (AlbumID) REFERENCES AlbumInfo(AlbumID),
FOREIGN KEY (ArtistID) REFERENCES ArtistInfo(ArtistID),
FOREIGN KEY (GenreID) REFERENCES GenreInfo(GenreID)

解决方案 #

1. 修改AlbumID字段允许为空

首先移除AlbumID的NOT NULL约束：

ALTER TABLE SongsInfo MODIFY COLUMN AlbumID int(11) NULL;

2. 添加条件约束

根据数据库版本选择合适的实现方式：

方式一：使用CHECK约束（MySQL 8.0.16+、PostgreSQL等支持CHECK的数据库）

直接添加CHECK约束实现逻辑校验：

ALTER TABLE SongsInfo
ADD CONSTRAINT chk_albumid_status CHECK (
    (status = 'Released' AND AlbumID IS NOT NULL)
    OR (status = 'Unreleased' AND AlbumID IS NULL)
);

方式二：使用触发器（MySQL 8.0.16之前版本）

对于不支持CHECK约束的旧版本MySQL，通过触发器实现校验：

创建插入前校验触发器：

DELIMITER //
CREATE TRIGGER trg_songs_insert_check
BEFORE INSERT ON SongsInfo
FOR EACH ROW
BEGIN
    IF (NEW.status = 'Released' AND NEW.AlbumID IS NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Released状态的歌曲必须指定AlbumID';
    END IF;
    IF (NEW.status = 'Unreleased' AND NEW.AlbumID IS NOT NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unreleased状态的歌曲不能指定AlbumID';
    END IF;
END //
DELIMITER ;

创建更新前校验触发器：

DELIMITER //
CREATE TRIGGER trg_songs_update_check
BEFORE UPDATE ON SongsInfo
FOR EACH ROW
BEGIN
    IF (NEW.status = 'Released' AND NEW.AlbumID IS NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Released状态的歌曲必须指定AlbumID';
    END IF;
    IF (NEW.status = 'Unreleased' AND NEW.AlbumID IS NOT NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unreleased状态的歌曲不能指定AlbumID';
    END IF;
END //
DELIMITER ;

3. 可选：限制status字段的取值范围

为避免status出现非法值，可将其设置为ENUM类型或添加CHECK约束：

-- 方式一：设置为ENUM类型
ALTER TABLE SongsInfo MODIFY COLUMN status ENUM('Released', 'Unreleased') NOT NULL;

-- 方式二：添加CHECK约束
ALTER TABLE SongsInfo ADD CONSTRAINT chk_status_value CHECK (status IN ('Released', 'Unreleased'));

内容的提问来源于stack exchange，提问作者Dillon Myrddin