Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to conditionally allow Nulls?

I have a table called, ‘Songs’. This is the table structure.

  • The table has an AlbumID column, which will be a foreign key reference to the Album table.
  • The Songs table also has a ‘status’ column. Where it can be either ‘Released’ or ‘Unreleased’.

Currently, the AlbumID column is NOT NULL. And has to be filled in.
However, I was curious if anything similar to something like a conditional null exists. I wanted it to be that the AlbumID field is only NOT NULL, if the status = "Released". If the song is released, then you have to enter the AlbumID. If the song is not released, AlbumID does not need to be filled.

Here is the structure of my SongsInfo table right now.

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)
Tags:

2

Answers


  1. 0

    You can get this behavior with a check constraint:

    CHECK (status = 'Unreleased' OR AlbumID IS NOT NULL)
    Login or Signup to reply.
  2. 0

    You can use a CHECK constraint, as in:

    create table albums (
  id int primary key not null,
  name varchar(50)
);

insert into albums (id, name) values (101, 'The Tree'), (102, 'The Mountain');

create table songs (
  id int primary key not null,
  album_id int references albums (id),
  name varchar(100),
  status varchar(10) check (status in ('Released', 'Unreleased')),
  check (status = 'Unreleased' or status = 'Released' and album_id is not null)
);

    Then, the following INSERTs succeed:

    insert into songs (id, album_id, name, status) values
  (10, null, 'Gold Branch', 'Unreleased'); -- null is OK since it's unreleased

insert into songs (id, album_id, name, status) values
  (11, 101, 'Red Branch', 'Released'); -- has an album since it's released

    But the following INSERT fails as expected:

    insert into songs (id, album_id, name, status) values
  (12, null, 'Green Branch', 'Released');
-- fails: cannot use null since it's released
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "CK__songs__3B75D760". The conflict occurred in database "fiddle_1a3d48d3aff44c1d924fc9a3596cf517", table "dbo.songs".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated

    See running example at db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search