How define column a OR column b NOT NULL?

Question

I would like to know if it's possible to create a not null constraint for either column name OR column surname. ex :

create table football_players (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(20),
    surname VARCHAR(20)
);

Sometimes we know the name or the surname but not both. I don't want a player without name in my database.

Answer to your question strictly depends on the exact RDBMS you're using. What is it - SQL Server, MySQL, Oracle, some other DBMS? — Andrey Korneyev, Nov 09 '15 at 14:44
You can't do that in MySQL - it still doesn't support check constraints (though it won't tell you that) — , Nov 09 '15 at 17:15

Gordon Linoff · Accepted Answer · 2015-11-09T17:18:53.183

2

The standard SQL method would be:

create table football_players (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(20),
    surname VARCHAR(20),
    constraint chk_name_surname check (name is not null or surname is not null)
);

This does not work in MySQL (the question was not originally tagged MySQL). Doing this in MySQL requires a trigger.

edited Nov 09 '15 at 17:18

answered Nov 09 '15 at 14:46

Gordon Linoff

1,242,037
58
646
786

You probably want to check for empty strings as well. For most people an empty string is not a "valid" name either. – Nov 09 '15 at 15:07
Yes i tried that, but when i test it on mysql, i can enter null for name and surname. – Robinson Nov 09 '15 at 15:42
@Robinson: that's because MySQL still doesn't support check constraints. – Nov 09 '15 at 17:15
`DELIMITER // CREATE TRIGGER trigger_players_name_not_null BEFORE INSERT ON players FOR EACH ROW BEGIN DECLARE dummy,baddata INT; SET baddata = 0; IF NEW.name is null and NEW.surname is null THEN SET baddata = 1; END IF; IF baddata = 1 THEN SELECT CONCAT('Cannot Insert This Because Age ',NEW.name, NEW.surname,' are Invalid') INTO dummy FROM information_schema.tables; END IF; END // DELIMITER ;` – Robinson Nov 09 '15 at 21:58

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

MySQL doesn't support check constraints, but you can do something similar with a trigger.

edited May 23 '17 at 12:23

Community

1
1

answered Nov 09 '15 at 17:20

Don Kirkby

53,582
27
205
286

score 0 · Answer 3 · answered Nov 09 '15 at 15:05

If I understood correctly:

You can ask if any data from PHP to load is null. For instance:

If (!isset($name)) {
    //code sql
}
else if (!isset($lastname)){
    //code sql
}
else {
    //code sql
}

and altering the table:

alter table TABLENAME modify FIELD varchar(40) not null default ''

or you can select IS NOT NULL:

SELECT LastName,FirstName FROM tablename
WHERE LastName IS NOT NULL

score 0 · Answer 4 · answered Nov 09 '15 at 22:06

DELIMITER //
CREATE TRIGGER trigger_players_name_not_null BEFORE INSERT ON players
   FOR EACH ROW BEGIN
   DECLARE dummy,baddata INT;
   SET baddata = 0;
      IF NEW.name is null and NEW.surname is null THEN SET baddata = 1;
      END IF;
      IF baddata = 1 THEN
         SELECT CONCAT('Cannot Insert This Because Age ',NEW.name, NEW.surname,' are Invalid')
         INTO dummy FROM information_schema.tables;
      END IF;
END
//
DELIMITER ;

How define column a OR column b NOT NULL?

4 Answers4