How do I add a constraint to this code where only certain characters can be inputted?

Question

complete novice to mysql but i'm having problems finding sources online that explain my problem. Essentially I need to add a constraint to the videoCode row where the identifier is a code in the form of XX## where the X are letters and the # are numbers.

Any help would be greatly appreciated.

CREATE TABLE videoTable(
    videoID INT,
    channelID INT,
    videoTitle VARCHAR(255) NOT NULL,
    videoPostedDate DATE NOT NULL,
    videoTags VARCHAR(255),
    noViews INT NOT NULL,
    videoCode VARCHAR(4) NOT NULL (ADD CONSTRAINT)

    PRIMARY KEY (videoID),
    FOREIGN KEY (channelID) REFERENCES channelTable(channelID)
);

What version of MySQL are you using? [Edit] the question and add the tag. — sticky bit, Mar 09 '20 at 22:48

score 5 · Answer 1 · answered Mar 09 '20 at 22:50

5

Use a CHECK constraint:

CREATE TABLE videoTable(
    videoID INT,
    channelID INT,
    videoTitle VARCHAR(255) NOT NULL,
    videoPostedDate DATE NOT NULL,
    videoTags VARCHAR(255),
    noViews INT NOT NULL,
    videoCode VARCHAR(4) NOT NULL CHECK(videoCode REGEXP '^[A-Za-z]{2}[0-9]+$')
    PRIMARY KEY (videoID),
    FOREIGN KEY (channelID) REFERENCES channelTable(channelID)
);

The regexp describes a string made of 2 alphabetic characters, followed by numbers only (at least one number must be present).

Note: this requires MySQL 8.0. In earlier versions, check constraints were parsed but ignored, and you would typically need a trigger for this.

answered Mar 09 '20 at 22:50

GMB

216,147
25
84
135

Just out of curiosity what was the purpose of parsing them if they were not checked? Seems odd to allow them to be valid syntax when they don't do anything. – Max Young Mar 09 '20 at 23:28
@MaxYoung: I agree, that's a tricky behavior. I imagine they put it that way because earlier versions were foreseeing this new functionality. But not a good API design choice in my opinion. – GMB Mar 10 '20 at 00:02
@RossKnight . . . This does depend heavily on the version. I don't think this is supported until 8.0.16, which is quite recent (and not merely 8.0). This is why specifying your version is so important. – Gordon Linoff Mar 10 '20 at 01:13

How do I add a constraint to this code where only certain characters can be inputted?

1 Answers1