• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3095
  • Last Modified:

Check constraints in MySQL

 I just want to confirm that I understand what it is said in the manuals about the CHECK clause on the CREATE TABLE statement.  It says that the clause is parsed but ignored.  In other words if I have this table:

CREATE table test (
  id int primary key,
  name char(10)
  check (id<10));

  I could insert a row with a value higher than 10 and it will not be rejected!

  Am I right?  Is this going to be implemented in the future?

Thanks,
0
granbajo
Asked:
granbajo
  • 5
  • 2
1 Solution
 
ajaikumarrCommented:
Hai,

I've taken the following lines from http://dev.mysql.com/doc/mysql/en/create-table.html

In MySQL 3.23.44 or later, InnoDB tables support checking of foreign key constraints. See Chapter 15, The InnoDB Storage Engine. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE  actions on foreign keys as of MySQL 3.23.50 and 4.0.8, respectively. For the precise syntax, see Section 15.7.4, “FOREIGN KEY Constraints”.

Bye
Ajai
0
 
ellandrdCommented:
here you go...

CREATE table test (
  id int
  constraint pk_id primary key,
  constraint check_id check (id<10),
  name char(10)
);

Ellandrd
 
0
 
ellandrdCommented:
plus its best to have name as varchar(10) instead of char(10)
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
granbajoAuthor Commented:
Ajai... thanks for that... but I am not looking for a FK constraint.  I would like for the database to prevent you from insertiing a row if you violate the constraints.

Ellandrd...

CREATE table test (
  id int
  constraint pk_id primary key,
  constraint check_id check (id<10),
  name char(10)
);

The statement doesn't work on my version of  MySQL (4.1.11),  but it wouldn't be too different from what I posted earlier... it seems to me that the check constraint is just being ignored!!!
0
 
ellandrdCommented:
not been smart, but you should upgrade. that way you have less bugs to hold you back...

these one of them...

Ellandrd

p.s im an looking into mysql 4.1.11 constraints for you...
0
 
ellandrdCommented:
any luck?
0
 
granbajoAuthor Commented:
OK, I take the last link to be confirmation of my original question, and that I have to wait for version 5 in order to have the constraints feature.   Unfortunately it is not GA yet!  I will  download it and try it.

Carlos
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now