CHECK won't work in MySQL?

Problems with the Windows version of XAMPP, questions, comments, and anything related.

CHECK won't work in MySQL?

Postby Mozza314 » 05. December 2008 04:03

Hi

I have just been learning SQL through http://www.w3schools.com/sql/, using XAMPP (I am also learning php).

Anyway, I try to run this query on the database, expecting an error because the entry violates the CHECK condition, but it goes through and I simply see the entry in the table which violates the CHECK -

INPUT:
Code: Select all
CREATE TABLE OldPersons
(
   Age INT(8) NOT NULL,
   CONSTRAINT chk_Person CHECK (Age>200)
);

INSERT INTO OldPersons VALUES (34);

SELECT * FROM OldPersons;


OUTPUT:
Code: Select all
Age
34



I've also tried using different ways of applying CHECK -

INPUT:
Code: Select all
CREATE TABLE OldPersons
(
   Age INT(8) NOT NULL,
   CHECK (Age>200)
);

INSERT INTO OldPersons VALUES (34);

SELECT * FROM OldPersons;


and

INPUT:
Code: Select all
CREATE TABLE OldPersons
(
   Age INT(8) NOT NULL
);

ALTER TABLE OldPersons ADD CHECK (Age>200);

INSERT INTO OldPersons VALUES (34);

SELECT * FROM OldPersons;


and

INPUT
Code: Select all
CREATE TABLE OldPersons
(
   Age INT(8) NOT NULL
);

ALTER TABLE OldPersons ADD CONSTRAINT chk_Person CHECK (Age>200);

INSERT INTO OldPersons VALUES (34);

SELECT * FROM OldPersons;


and

INPUT:
Code: Select all
CREATE TABLE OldPersons
(
   Age INT(8) NOT NULL CHECK Age>200
);

INSERT INTO OldPersons VALUES (34);

SELECT * FROM OldPersons;


With this last one only supposed to work on SQL Server, Oracle, MS Access, but I tried it anyway. All of them gave the same output with the violating row going through. I find this really weird because PhpMyAdmin recognises the CHECK keyword in its syntax highlighting. See http://www.w3schools.com/sql/sql_check.asp

Please help!
Mozza314
 
Posts: 1
Joined: 05. December 2008 03:40

Re: CHECK won't work in MySQL?

Postby Wiedmann » 05. December 2008 08:41

Anyway, I try to run this query on the database, expecting an error because the entry violates the CHECK condition, but it goes through and I simply see the entry in the table which violates the CHECK -

From the MySQL manual (CREATE TABLE):
MySQL manual wrote:The CHECK clause is parsed but ignored by all storage engines.


(BTW: constraints in general only work with InnoDB tables: "CREATE TABLE ... ENGINE=InnoDB")
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 75 guests