Reputation: 5263
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?
Upvotes: 0
Views: 1743
Reputation: 1
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
Upvotes: 0
Reputation: 204904
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.
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.
Upvotes: 1
Reputation: 11
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
Upvotes: 1