List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 21 2006 7:15pm
Subject:Re: Constraint checking
View as plain text  
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

Thread
Constraint checkingBarbara Deaton21 Mar
  • Re: Constraint checkingMartijn Tonies21 Mar