Bug #50525 CHECK clause on CREATE TABLE not supported - Table constraint missing?

• Submitted: 21 Jan 2010 21:56
• Modified: 22 Jan 2010 17:18
• Reporter: James Couhlin
• Status: Won't fix
• Category: MySQL Workbench
• Severity: S3 (Non-critical)
• Version: 5.2.11
• OS: Any
• CPU Architecture: Any

[21 Jan 2010 21:56] James Couhlin

Description:
SQL syntax supports

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)
)

How is CHECK(P_Id > 0) supported in the workbench?

How to repeat:
Open the workbench.
Create an ERR model.
Create / edit a table with the above attributes.
Where is the CHECK constraint entered?

[22 Jan 2010 4:01] Peter Laursen

The MySQL server does not really support CHECK constraint.  You may specify it and it is parsed - but ignored.  See this:

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));

SHOW CREATE TABLE Persons;
/* returns

CREATE TABLE `persons` (
  `P_Id` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

Peter
(not a MySQL person)

[22 Jan 2010 17:18] Johannes Taxacher

Thanks for the answer Peter,

thats why we don't support it in WB either.