I am currently working on some school work in SQL. Normally I would ask my boss for help but here I am as he is on vacation. I am trying to create a table with horse names, for some reason I keep getting this error>

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<=20.0), BirthDate DATE CHECK (BirthDate >= "2015-01-01"), PRIMARY KEY (' at line 5

I am not sure what I am doing wrong and would appreciate some help.

CodePudding user response：

You can do:

create table horse (
  id int primary key not null auto_increment,
  registered_name varchar(15) not null,
  breed varchar(20) not null check (breed in ('Egyptian Arab',
    'Holsteiner', 'Quarter Horse', 'Paint', 'Saddlebard')),
  height decimal(3, 1) check (height >= 10.0 and height <= 20.0),
  birth_date date check (birth_date >= '2015-01-01')
);

Keep in mind that:

• CHECK constraints are only honored in MySQL 8.0.16 and newer. On previous versions they are accepted and silently ignored.
• The way you are checking breeds is static and is difficult to change. It would be more flexible to have a separate table breed and include a foreign key in this table.

CodePudding user response：

Height >= 10.0 and <= 20.0

should be

Height BETWEEN 10.0 and 20.0