CODEWITHSUNDEEP
mysqlsql
StackOverFlow1132
StackOverFlow1132

Reputation: 35

mysql ignoring check constraint

I have this code and I want to implement it in MySQL but CHECK is not supported. How would I do it?

CREATE TABLE Fare (
    type_of_passenger varchar(20),
    price FLOAT,
    PRIMARY KEY (type_of_passenger),
    CHECK (lower(type_of_passenger)='child' OR lower(type_of_passenger)='student' OR lower(type_of_passenger)='senior')
);

Upvotes: 1

Views: 250

Answers (2)

Barmar
Barmar

Reputation: 781370

It looks like you're really trying to implement an ENUM:

type_of_passenger ENUM('child', 'student', 'senior')

By default MySQL doesn't validate ENUM values, so if you try to store something else it will store an empty string, but if strict SQL mode is enabled it will report an error.

Another alternative is to make this a foreign key to a table where you enter the valid values.

CREATE TABLE passenger_types (
    type VARCHAR(20) NOT NULL PRIMARY KEY
);
INSERT INTO passenger_types (type) VALUES
('child'), ('student'), ('senior');


CREATE TABLE Fare (
    type_of_passenger varchar(20),
    price FLOAT,
    PRIMARY KEY (type_of_passenger),
    CONSTRAINT FOREIGN KEY (type_of_passenger) REFERENCES passenger_types (type)
);

Upvotes: 1

Arth
Arth

Reputation: 13110

Are you sure you need that check? As the primary key is type_of_passenger you will only ever be able to have three rows.. this seems like over-kill

You could just INSERT those 3 rows and move on. If you reference this field in a foreign key, you'll be restricted to the values in the table anyway

In fact as soon as you reference each value with a foreign key that uses ON UPDATE RESTRICT and ON DELETE RESTRICT you won't be able to change them anyway

The only valid concern I can see here is that you want to allow a DB user to change the price but not the type_of_passenger

If you INSERT the correct (or stub) data to start with, you can then control table and column access via permissions

N.B. I would use a surrogate unsigned integer primary here and unique the string description, thus if I do need to change the string I can do it without worry, and without the performance hit of updating all the tables that reference it

Upvotes: 1

Related Questions