PHP/MySQL: how to prevent insertion of a row with all columns = NULL?
I'm trying to use this trigger in a PHP form. I've tried the following, but I still get null rows inserted into my table when I submit the form.
开发者_运维百科$trigger="DELIMITER $$
CREATE TRIGGER check_not_all_null BEFORE INSERT ON agent FOR EACH ROW
BEGIN
IF COALESCE(first_name, last_name, affiliation) IS NOT NULL THEN
CALL fail('All fields cannot be null');
END IF;
END $$
DELIMITER ; ";
$execute = mysql_query($trigger);
$query4 = "INSERT INTO agent
(first_name, last_name, affiliation)
VALUES
('$first_name', '$last_name', '$affiliation')";
$result4 = mysql_query($query4, $connection) or die('Error querying database.');
First off, I think you mean
IF COALESCE(first_name, last_name, affiliation) IS NULL
because NOT NULL
would mean that one of the values (at least) was not null.
Second, @tofutim is correct; empty strings are not the same as NULL
values.
If I understand your logic shouldn't this line:
IF COALESCE(first_name, last_name, affiliation) IS NOT NULL THEN
Be
IF COALESCE(first_name, last_name, affiliation) IS NULL THEN
Otherwise, you'll fail only when there is a value that isn't null.
The trigger needs to have the new.
virtual table specified otherwise it will never work.
You might want to consider changing your trigger into.
CREATE TRIGGER check_not_all_null BEFORE INSERT ON agent FOR EACH ROW
BEGIN
SET new.first_name = NULLIF(new.first_name, '');
SET new.last_name = NULLIF(new.last_name, '');
SET new.affiliation = NULLIF(new.affiliation , '');
IF COALESCE(new.first_name, new.last_name, new.affiliation) IS NULL THEN
CALL fail('All fields cannot be empty');
END IF;
END $$
This will force empty strings into null
's.
See: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_nullif
And: http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html
Let's say that your trigger, as modified according to the excellent answers already provided, works to guarantee that at least one attribute of the set {first_name, last_name, affiliation} is not null. Assuming you also have a ID number surrogate key, that table might allow data like this. (I don't know what you mean by affiliation, so I'll just use a char(1) for that.)
ID first_name last_name affiliation
--
1 NULL NULL A
2 NULL NULL A
3 NULL Nethery B
4 Clare NULL C
5 Christian Guilbert A
6 Christian Guilbert A
7 Christian Armistead A
8 Hugh Marchal B
9 Sharron Sevier A
10 Christian Guilbert NULL
Let's say that your name is "Christian Guilbert". Only one of those rows (one of those surrogate ID number) is yours. Which one is it?
Think about whether this is really what you want to do. My first rule of thumb for database constraints: everything the dbms allows will eventually find its way into your database. (I once saw a Fortune 500 IT department release an app that allowed users to enter negative dimensions for shipping containers. You can fit a lot of boxes into a railroad car that way. On paper.)
IMHO, every one of those three columns should be declared NOT NULL. And I'm gonna upvote everyone else who says that, too.
(Names courtesy of The Random Name Generator.)
精彩评论