List:General Discussion« Previous MessageNext Message »
From:mfatene Date:May 21 2005 3:34pm
Subject:Re: constraints
View as plain text  
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=1
>
>


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