From: Martijn Tonies
Date: March 21 2006 7:15pm
Subject: Re: Constraint checking
List-Archive: http://lists.mysql.com/mysql/196028
Message-Id: <001401c64d1b$cf9768a0$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hello Barbara,


> When does constraint checking occur?
>
> I can create a table that has a constraint on a column that doesn't exist.
I would expect an error on the table creation, but the table gets created
successfully.
>
> I'm running on windows 5.0.18 client/server.
>
> mysql> show create table product;
>
+---------+-----------------------------------------------------------------
------------------+
> | Table   | Create Table      |
>
+---------+-----------------------------------------------------------------
------------------+
> | product | CREATE TABLE "product" (  "category" int(11) NOT NULL,  "id"
int(11) NOT NULL,
>   "price" decimal(10,0) default NULL,  PRIMARY KEY  ("category","id"))
>
+---------+-----------------------------------------------------------------
------------
>
> mysql> create table barbconstr (col1 int(11), constraint ts_con5 check
(product.
> junk > 0));
> Query OK, 0 rows affected (2.63 sec)
>
>
> I expected this to fail with a column "junk" doesn't exist, type of
message.

MySQL doesn't support check constraints.

It supports the syntax to create one, but fully ignores whatever you put
in there. This is a "let's make the scripts compatible with other systems"
feature.

> The question is, why is this valid?  I hate to mention it, but this is who
I have to compare my apps behavior to, SQL Server will return the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column 'COL7' is
specified in a constraint or computed-column definition.
>

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com