CODEWITHSUNDEEP
mysqlsqloracle-databasecreate-tablecheck-constraints
Ross Knight
Ross Knight

Reputation: 39

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

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)
);

Upvotes: 3

Views: 386

Answers (1)

GMB
GMB

Reputation: 222432

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.

Upvotes: 5

Related Questions