Bug #60398 SHOW CREATE TABLE --- missing CHECK constraint

• Submitted: 8 Mar 2011 21:02
• Modified: 9 Mar 2011 0:32
• Reporter: Arthur Yeo
• Status: Not a Bug
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.5.8
• OS: Windows (Windows 7 - 64bit)
• CPU Architecture: Any
• Tags: check, constraint

[8 Mar 2011 21:02] Arthur Yeo

Description:
When a TABLE is created with a table constraint using the CHECK option, it is not reproduced when: "SHOW CREATE TABLE mytable" is called.

Although I have not tested this against the Workbench product, I have a feeling the bug is present there as well.

How to repeat:
a) 
DROP TABLE IF EXISTS `mytable` ;

CREATE  TABLE IF NOT EXISTS `mytable` (
  `AccountId` INT NOT NULL ,
  `SomeField` DECIMAL(18,5) NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`AccountId`),
  check (SomeField > 0)
)
ENGINE = InnoDB;

b)show create table mytable;
will produce this CREATE TABLE stmt without the CHECK constraint
'CREATE TABLE `mytable` (
  `AccountId` int(11) NOT NULL,
  `SomeField` decimal(18,5) NOT NULL DEFAULT ''0.0'',
  PRIMARY KEY (`AccountId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

[8 Mar 2011 22:20] Peter Laursen

MySQL server does not support CHECK constraints (only PRIMARY and UNIQUE constraints are supported). You may specify a CHECK constraint at CREATE-time but it is ignored by the server/parser.

Peter
(not a MySQL person)

[9 Mar 2011 0:32] MySQL Verification Team

Please see Peter's prior comment. Thanks.

[9 Mar 2011 6:02] MySQL Verification Team

if you believe that mysql should return an error or warning for ignored syntax, take a look at bug #47771