Adding CHECK constraint Syntax

Question

I keep getting a syntax error even though I reviewed different formats that the CHECK constraint can be in. What am I doing wrong?

ALTER TABLE `eventsdataview`
ADD PitchArea CHAR(10) DEFAULT 'UNKNOWN' NOT NULL, CHECK (PitchArea IN ('Area1', 'Area2', 'Area3', 'Area4'));
ALTER TABLE `trackdataview`
ADD PitchArea CHAR(10) DEFAULT 'UNKNOWN' NOT NULL, CHECK (PitchArea IN ('Area1', 'Area2', 'Area3', 'Area4'));

What database are you using? The backticks suggest MySQL. If so, check constraints are not actually checked, so you can just drop them from the query. — Gordon Linoff
– Gordon Linoff, Commented Oct 21, 2019 at 12:07
DEFAULT 'UNKNOWN' NOT NULL looks a bit odd, as in ANSI/iSO SQL specs NULL actually means "unknown" — Raymond Nijland
– Raymond Nijland, Commented Oct 21, 2019 at 12:10

Gordon Linoff · Accepted Answer · 2019-10-21 12:19:40Z

1

The correct syntax drops the comma, because you are trying to express an in-line constraint. So:

ALTER TABLE `eventsdataview`
    ADD PitchArea CHAR(10) DEFAULT 'UNKNOWN' NOT NULL
         CHECK (PitchArea IN ('Area1', 'Area2', 'Area3', 'Area4'));

As I mention in a comment, MySQL only very recently added support check constraints, so this may be for information purposes only. If you want to validate the values, use an enum or foreign key.

edited Oct 21, 2019 at 12:19

answered Oct 21, 2019 at 12:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user330315 Over a year ago

MySQL 8.0.16 finally added support for check constraints

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . I was hoping it would go into MySQL 8 from the get-go, so I wouldn't have to try to remember arcane versioning.

user330315 Over a year ago

Yes, MySQL's policy to introduce new features with minor (patch) releases is really confusing.

Raymond Nijland Over a year ago

"MySQL 8.0.16 finally added support for check constraints" @a_horse_with_no_name already "supported" CHECK for much longer but not in CREATE TABLE, you would need a create a updateble VIEW with check option..

user330315 Over a year ago

@RaymondNijland: that's not a check constraint. The with check option only ensures that only the rows that the view returns can also be updated or deleted. That is something completely different

|

Collectives™ on Stack Overflow

Adding CHECK constraint Syntax

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related