My Sql Query to check country name is restricted

Question

CREATE TABLE
IF NOT EXISTS countrie (
    Country_id VARCHAR (30) NOT NULL,
    Country_name VARCHAR (40) CHECK (
        Country_name IN ('Italy', 'India', 'China')
    ),
    region_id DECIMAL (10, 2) NOT NULL
);

I have written this query where user will not be able to enter country name except Italy,India & China.But I am able to enter other country into my table. Please guide me on this query.

score 1 · Answer 1 · answered May 04 '16 at 08:43

1

Set the Country_name enum with value ('Italy','India','China')

answered May 04 '16 at 08:43

Dipanwita Kundu

1,637
1
9
14

score 0 · Answer 2 · edited May 23 '17 at 12:31

The CHECK clause is parsed but ignored by all storage engines. See Section 12.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, “MySQL Differences from Standard SQL”.

try using trigger . more answers in here : CHECK constraint in MySQL is not working

My Sql Query to check country name is restricted

2 Answers2