Unable to add check constraint to a column

Question

I am trying to create a simple table with a few columns. I would like to add a check constraint to one of the columns, see below:

create table jobs (
    JOB_ID varchar(10) not null,
    JOB_TITLE varchar(35) not null,
    MIN_SALARY decimal(6,0),
    MAX_SALARY decimal(6,0) 
    CHECK(MAX_SALARY<=25000)
    );

This returns an error for the CHECK line.

A comma or closing bracket was expected. (near CHECK)

If I add a comma like to the previous line; MAX_SALARY decimal(6,0),, I am returned with more errors:

A symbol name was expected! A reserved keyword can not be used as a column name without backquotes.

Unexpected beginning of statement near MAX_SALARY

Unexpected beginning of statement near 25000

@Hackit I'm new to this so I'm not too sure on the terminology. But I am running phpMyAdmin via xampp — August Williams, Oct 14 '17 at 17:44
Possible duplicate of [sql CHECK constraint not working properly](https://stackoverflow.com/questions/34132644/sql-check-constraint-not-working-properly) MySQL does not support check constraints! — Lord Peter, Oct 14 '17 at 17:47
First Just Create The Simple Table And Then Using Alter Try to Add Check like as update my ans !! — Hack it, Oct 14 '17 at 17:47
I'm just so lost here, this is an example given to me by my tutor and it doesn't work. — August Williams, Oct 14 '17 at 17:50
@Hackit As Lord Peter has said, I believe MySQL does not support check constraint, — August Williams, Oct 14 '17 at 17:56
@AugustWilliams Yes ! `CHECK` constraints are not supported by MySQL — Hack it, Oct 14 '17 at 17:58

Hack it · Accepted Answer · 2017-10-14T18:00:52.957

3

Updated:

Just Create Table Without CHECK and Then After Alter it:

  create table jobs (
    JOB_ID varchar(10) not null,
    JOB_TITLE varchar(35) not null,
    MIN_SALARY decimal(6,0),
    MAX_SALARY decimal(6,0)
    );

ALTER TABLE jobs ADD CHECK (MAX_SALARY<=25000);

OR

In MySQL:

create table jobs (
    JOB_ID varchar(10) not null,
    JOB_TITLE varchar(35) not null,
    MIN_SALARY decimal(6,0),
    MAX_SALARY decimal(6,0), 
    CHECK(MAX_SALARY<=25000)    
    );

SQL Server / Oracle

 create table jobs (
    JOB_ID varchar(10) not null,
    JOB_TITLE varchar(35) not null,
    MIN_SALARY decimal(6,0),
    MAX_SALARY decimal(6,0) CHECK(MAX_SALARY<=25000)

    );

NOTE: CHECK constraints are not supported by MySQL But You Can Use it, as i declared , but its not give you an output !!

edited Oct 14 '17 at 18:00

answered Oct 14 '17 at 17:27

Hack it

405
2
10

All of these examples return me with similar errors. – August Williams Oct 14 '17 at 17:42
@AugustWilliams Can You Try With Alter table ? – Hack it Oct 14 '17 at 17:43
6

`CHECK` conflict with `ADD` – August Williams Oct 14 '17 at 17:52
@AugustWilliams Its Completely Work On My Local !! – Hack it Oct 14 '17 at 17:56

Unable to add check constraint to a column

1 Answers1

Linked