List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:May 21 2005 3:47pm
Subject:Re: constraints
View as plain text  
The manual is your friend!  CHECK constraints are not supported in 
mysql.  "The CHECK clause is parsed but ignored by all storage engines." 
<http://dev.mysql.com/doc/mysql/en/create-table.html>.

Using your example, you can easily verify this with SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE employee;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table 
 
         |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
   `name` varchar(30) default NULL,
   `salary` decimal(10,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

As you can see, there is no check constraint.

Michael

mfatene@stripped wrote:

> Hi,
> The syntaxe is here, but the constraints seems not to be checked. Is this a bug
> ? i don't know !
> 
> mysql> create table employee (
>     ->      name varchar(30),
>     ->      salary numeric(10,2),
>     ->      constraint  check (salary > 0)
>     ->   );
> Query OK, 0 rows affected (0.22 sec)
> 
> mysql>
> mysql>
> mysql> desc employee
>     -> ;
> +--------+---------------+------+-----+---------+-------+
> | Field  | Type          | Null | Key | Default | Extra |
> +--------+---------------+------+-----+---------+-------+
> | name   | varchar(30)   | YES  |     | NULL    |       |
> | salary | decimal(10,2) | YES  |     | NULL    |       |
> +--------+---------------+------+-----+---------+-------+
> 2 rows in set (0.03 sec)
> 
> mysql> insert into employee value('name 1',0),('name 2',1000)
>     -> ;
> Query OK, 2 rows affected (0.05 sec)
> Records: 2  Duplicates: 0  Warnings: 0
> 
> mysql> select * from employee;
> +--------+---------+
> | name   | salary  |
> +--------+---------+
> | name 1 |    0.00 |
> | name 2 | 1000.00 |
> +--------+---------+
> 2 rows in set (0.02 sec)
> 
> Not null is checked :
> mysql> insert into employee value('name 1',null);
> ERROR 1048 (23000): Column 'salary' cannot be null
> 
> But not a not '0' check constraint.
> 
> Mathias
> 
> Selon Rhino <rhino1@stripped>:
> 
> 
>>Are you sure this kind of constraint is supported in MySQL 4.1?
>>
>>I'm not saying they aren't, I just don't remember. Unless you're sure they
>>are, you should check the manual.
>>
>>Rhino
>>
>>----- Original Message -----
>>From: "Rodrigo Sakai" <rodrigo.sakai@stripped>
>>To: <mysql@stripped>
>>Sent: Friday, May 20, 2005 6:49 PM
>>Subject: constraints
>>
>>
>>  Hi, I need to do a constraint that checks if the field 'salary' of one
>>table is not smaller than zero. Like:
>>
>>  create table employee (
>>
>>     name varchar(30),
>>     salary numeric(10,2)
>>
>>     constraint  ck_salary check (salary > 0)
>>  );
>>
>>  What´s the sintaxe? I'am not finding the correct sintaxe to do this
>>constraint.
>>
>>  I'm using MySQL 4.1

Thread
constraintsRodrigo Sakai21 May
  • Re: constraintsRhino21 May
    • Re: constraintsmfatene21 May
      • Re: constraintsMichael Stassen21 May
        • Re: constraintsmfatene21 May
      • Re: constraintsPeter Brawley21 May