SQL Constraint issue

Question

Really new to SQL. I’m having some issues figuring out a constraint’

Here's what I want to do. I have a table with 2 columns CampusID and Location. I want a constraint that each element of the CampusID must be 'UMS' followed by two digits. Not sure how to code this. This is what I have done. I am using SQL Fiddle to do this.

CREATE TABLE IF NOT EXISTS Campuses(
    CampusID varchar(16) Primary Key,
    Location varchar(16) NOT NULL,
    CONSTRAINT CampusID Check CampusID LIKE (UMS[0-9]0-9]),
);

INSERT INTO Campuses VALUES 
    (“UMS01”,“Augusta”), 
    (“UMS02”,“Farmington”),
    (“UMS03”,“Fort Kent”),
    (“UMS04”,“Machais”),
    (“UMS05”,“Orono”),
    (“UMS06”,“Presque Isle”),
    (“UMS07”,“Southern Maine”);

MySQL does not enforce check constraints, so your code won't do anything. — Gordon Linoff, Nov 17 '17 at 02:42

Gordon Linoff · Answer 1 · 2017-11-17T02:48:47.627

MySQL does not actually implement check constraints. It does accept them syntactically, but it ignores them.

Just about all other databases do support them. For your table, the syntax would look like:

CREATE TABLE Campuses (
    CampusID varchar(16) Primary Key,
    Location varchar(16) NOT NULL,
    CONSTRAINT CampusID Check (CampusID LIKE 'UMS__')
);

Note: This is two characters after the UMS. Two digits is database dependent. For Postgres, you can use (CampusID REGEXP '^UMS[0-9][0-9]$').

Here is a SQL Fiddle using Postgres.

Notes:

Often, the primary key is auto incremented (called serial in Postgres).
The constraint name should not just be the name of the column. I would call it "chk_campuses_campusId".
The LIKE pattern needs to be surrounded by single quotes.
The entire check constraint expression needs to be enclosed in parentheses.

SQL Constraint issue

1 Answers1