0

I wrote schema:

CREATE TABLE user
(
user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(20) NOT NULL,
email VARCHAR(30) NOT NULL,
password VARCHAR(60) NOT NULL,
gender ENUM('M', 'F'),
CONSTRAINT pk_user_id PRIMARY KEY (user_id)

)

But when inserting only two values, lets say the users first name and last name, the email and password are left blank, meaning the not null is not working as expected. I do get 2 warnings when I show them, but the behavior is not as expected. Do I have to specifically make constraints to fail an insert to make sure this never happens?

Andy
  • 10,553
  • 21
  • 75
  • 125

1 Answers1

0

Unfortunately, mysql does not support CHECK constraints.

They are "valid syntax" in mysql only for compatibility reasons, so you can code them, and the create table SQL will execute OK, but they are ignored thereafter.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Really. Thats a shame. I mean, its not a big deal as I would be checking to make sure user input is there before inserting, but still, it adds a bit of extra security in terms of how input gets in. – Andy Apr 22 '12 at 18:49
  • I figure that when a warning is issued, is there a way to access that warning outside the database, like maybe through php or another scripting language? – Andy Apr 22 '12 at 18:54
  • What warning would you be expecting? – Bohemian Apr 22 '12 at 23:35
  • Something that allows me to at least know it happened to do something incase. – Andy Apr 23 '12 at 01:23
  • You could define a trigger that fires on update and/or insert that calls `RAISE EXCEPTION 'blank value for username'` (eg). Or the trigger could add the id of the offending record to a "problem record" table – Bohemian Apr 23 '12 at 04:18