"Horse Table" in a MySQL database is not working?

Question

I want to create a database with a table with the following criteria and constraints:

• ID - integer with range 0 to 65 thousand, auto increment, primary key
• RegisteredName - variable-length string with max 15 chars, not NULL
• Breed - variable-length string with max 20 chars, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred
• Height - number with 3 significant digits and 1 decimal place, must be ≥ 10.0 and ≤ 20.0
• BirthDate - date, must be ≥ Jan 1, 2015

So far I have wrote this

CREATE TABLE horse (
    ID SMALLINT AUTO_INCREMENT PRIMARY KEY,
    RegisteredName VARCHAR(15) NOT NULL,
    Breed VARCHAR(20), CHECK (Breed="Egyptian Arab" "Holsteiner" "Quarter Horse" "Paint" "Saddlebred")
    Height DECIMAL(3,1) CHECK (Height=>10.0) CHECK (Height<=20.0),
    BirthDate DATE CHECK (BirthDate=>"Jan 1, 2015")
);

After reading all of the suggestions and input you provided I corrected my code to be as follows.

CREATE TABLE Horse (
    ID SMALLINT ***UNSIGNED*** AUTO_INCREMENT PRIMARY KEY,
    RegisteredName VARCHAR(15) NOT NULL,
    Breed VARCHAR(20) CHECK (Breed="Egyptian Arab" "Holsteiner" "Quarter Horse" "Paint" "Saddlebred")***,***
    Height DECIMAL(3,1) CHECK ***(Height between 10.0 AND 20.0)***,
    BirthDate DATE CHECK ***(BirthDate >='2015-01-01')***
);

On line 2 I added UNSIGNED, on line 4 I moved the comma, on line 5 I removed an extra check statement, my incorrectly formatted inequalities and rewrote it using between instead. I corrected the 6th line to use the proper date format.

As a result workbench was able to properly execute and added my table to the schema on the left side bar.

Answer 1

You said not to do this for you, so I'll just link you to relevant MySQL documentation and you can read them.

ID SMALLINT AUTO_INCREMENT PRIMARY KEY,

If you want it to be unsigned so it supports values 0 - 65535, you need to use SMALLINT UNSIGNED. See https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html

A signed SMALLINT can have values from -32768 to 32767. That is, the same total number of values (2¹⁶), but half of them are negative.

CHECK (Breed="Egyptian Arab" "Holsteiner" "Quarter Horse" "Paint" "Saddlebred"),

If you want to compare to multiple values, use the IN(...) operator.

But I'd recommend using a lookup table instead of baking the list of horse breeds into your table definition. Using a lookup table is more flexible because you can add or remove values more easily, and each breed may need to have other attributes too.

Height DECIMAL(3,1) CHECK (Height=>10.0) CHECK (Height<=20.0),

MySQL supports an inequality operator >= but does not support a synonym operator =>. See documentation for operators: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_greater-than-or-equal

In fact, I don't know any programming language that supports => or =< as inequality operators. They tend to support >= and <=. See https://en.wikipedia.org/wiki/Relational_operator#Standard_relational_operators

But even better for this case, use the BETWEEN operator. That way you don't need two inequality conditions.

BirthDate DATE CHECK (BirthDate=>"Jan 1, 2015")

Another use of => that should be >=.

MySQL doesn't understand that format for dates. You should use dates in YYYY-MM-DD format. See: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

Another option is to parse a string in the format you show into a MySQL-compatible date by using the STR_TO_DATE() function, but it's easier to just use the standard MySQL date format.

One more tip: You will thank yourself later if you learn the right types of quotes to use in SQL. See: When to use single quotes, double quotes, and backticks in MySQL

Answer 2

This is what I entered:

CREATE TABLE Horse (

   ID SMALLINT UNSIGNED AUTO_INCREMENT,

   RegisteredName VARCHAR(15) NOT NULL,

   Breed VARCHAR(20) CHECK (Breed="Egyptian Arab" "Holsteiner" "Quarter Horse" "Paint" "Saddlebred"),

   Height DECIMAL(3,1) CHECK (HEIGHT BETWEEN '10.1' AND '19.9'),

   BirthDate DATE CHECK (birthdate > '2015-01-01'),

   PRIMARY KEY (ID)

   );

Answer 3

CREATE TABLE Horse (
    ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    RegisteredName VARCHAR(15) NOT NULL,
    Breed VARCHAR(20) CHECK (Breed IN ('Egyptian Arab', 'Holsteiner', 'Quarter Horse', 'Paint', 'Saddlebred')),
    Height DECIMAL(3,1) CHECK (Height between 10.0 AND 20.0),
    BirthDate DATE CHECK (BirthDate >= '2015-01-01')
);