0

I'm going to use mysql constraints to prevent inserting numbers less than zero. I found this query from W3schools.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

But when I insert 0 It allows me to do this. Any idea?

Vahid Najafi
  • 4,654
  • 11
  • 43
  • 88
  • [**See how check constraint is achieved through trigger in mysql**](http://stackoverflow.com/questions/37904425/constant-column-value-in-mysql-table/37904564#37904564) – 1000111 Aug 06 '16 at 11:43

3 Answers3

1

Currently MySQL does not support check which means it parses your definition but ignores the check part.

The CHECK clause is parsed but ignored by all storage engines.

The docs

To achieve the same you can define a trigger and cancel the update/insert if necessary with a SIGNAL

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid P_Id';

But your actual problem here is that you don't define P_Id as auto-increment PRIMARY KEY. Then the DB does all for you.

Then you don't provide a P_Id at all. The DB numbers this column starting from 1.

And you actually should not use W3Schools. Their toturials are bad.

Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”.

you can refer this answer CHECK constraint in MySQL is not working

Community
  • 1
  • 1
Jignesh Nakrani
  • 11
  • 2
0

Option 2, use generated column in combination with a CASE statement and not null constraint. For instance:

CREATE TABLE Persons
(
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 CHECK_GEN_FIELD int AS (CASE WHEN P_Id>0 THEN 1 ELSE NULL END) NOT NULL
);

Query OK, 0 rows affected (0.04 sec)

mysql> insert into Persons (P_Id,LastName) values (10,'test');

Query OK, 1 row affected (0.00 sec)

mysql> insert into Persons (P_Id,LastName) values (0,'test');

ERROR 1048 (23000): Column 'CHECK_GEN_FIELD' cannot be null

Serhii Kushnir
  • 1