From: Peter Brawley Date: May 21 2005 4:00pm Subject: Re: constraints List-Archive: http://lists.mysql.com/mysql/184407 Message-Id: <428F5B01.6060003@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-428F5B0131FA=======" --=======AVGMAIL-428F5B0131FA======= Content-Type: multipart/alternative; boundary=------------040302040101070103050702 --------------040302040101070103050702 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Mathias, >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) 13.2.6 of the 5.0.4 manual says "The CHECK clause is parsed but ignored by all storage engines." PB ----- 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 : > > > >>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" >>To: >>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 >> >> >>---------------------------------------------------------------------------- >>---- >> >> >>No virus found in this incoming message. >>Checked by AVG Anti-Virus. >>Version: 7.0.322 / Virus Database: 266.11.12 - Release Date: 17/05/2005 >> >> >> >>-- >>No virus found in this outgoing message. >>Checked by AVG Anti-Virus. >>Version: 7.0.322 / Virus Database: 266.11.12 - Release Date: 17/05/2005 >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/mysql?unsub=mfatene@stripped >> >> >> >> > > > > > --------------040302040101070103050702 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Mathias,

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

13.2.6 of the 5.0.4 manual says "The CHECK clause is parsed but ignored by all storage engines."

PB

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

----------------------------------------------------------------------------
----

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 266.11.12 - Release Date: 17/05/2005

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 266.11.12 - Release Date: 17/05/2005

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mfatene@stripped

--------------040302040101070103050702-- --=======AVGMAIL-428F5B0131FA======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.322 / Virus Database: 266.11.14 - Release Date: 5/20/2005 --=======AVGMAIL-428F5B0131FA=======--