List:General Discussion« Previous MessageNext Message »
From:mfatene Date:May 21 2005 3:50pm
Subject:Re: constraints
View as plain text  
>> The manual is your friend!
I was trying to inform you but you were faster  :o)
An IF then insert in your application can do the trick.

Mathias

Selon Michael Stassen <Michael.Stassen@stripped>:

> 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