0

hi i am trying to use CHECK constraint form MySQL , i got an example from w3 schools.

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

http://www.w3schools.com/sql/sql_check.asp

but it is not working as expected , the validation is ignored , after searching some time i found that The CHECK clause is parsed but ignored by all storage engines.

refrence :- CHECK constraint in MySQL is not working

then i searched for an answer , then i got this

Mysql CHECK Constraint

it says that you can use a trigger when insert an update the record , alternatively to the check constraint .

i have 2 questions .

1.what is the best alternative for CHECK constraint in MySQL?

2.what is the purpose of only parsing CHECK constraint with out using it . why is that exist if it is no use ??

Community
  • 1
  • 1
Kanishka Panamaldeniya
  • 17,302
  • 31
  • 123
  • 193
  • 1
    That's how MySQL works. There are several other places where MySQL silently ignores features it does not support (or silently truncates data which doesn't fit into a column). –  Mar 20 '13 at 09:15
  • 4
    [w3school is complete and utter garbage](http://www.complaintsboard.com/complaints/w3schoolscom-this-website-is-complete-and-utter-garbage-c630826.html) – Vishal Suthar Mar 20 '13 at 09:16
  • @a_horse_with_no_name thanks for the comment , is there any altermatve in mysql to do what check constraint does ?? – Kanishka Panamaldeniya Mar 20 '13 at 09:16
  • 3
    "*what is the best alternative for `CHECK` constraint in `MySQL`?*" - [triggers](http://dev.mysql.com/doc/en/triggers.html), like you already said in your question. "*what is the purpose of only parsing `CHECK` constraint with out using it . why is that exist if it is no use ??*" - to ensure that SQL-standard DDL will parse without error whilst noone has yet written the code to implement working constraints. – eggyal Mar 20 '13 at 09:18
  • 2
    @eggyal: the behaviour "*parse without error*" is really a bug in my opinion. If a DDL statement was succesfull, everything specified in there should be active. I think MySQL should throw an error that it doesn't support this feature - as every other DBMS does. –  Mar 20 '13 at 09:19
  • 2
    @KanishkaPanamaldeniya: alternative? Upgrade to Postgres maybe? –  Mar 20 '13 at 09:20
  • @a_horse_with_no_name: I don't disagree. – eggyal Mar 20 '13 at 09:20
  • Is it just me or does it look as though P_Id should be an auto-increment field? – Cups Mar 20 '13 at 09:24

0 Answers0