Set some field 'NOT NULL' only if a field has a special value

Question

CREATE TABLE Persona(
CF VARCHAR(16) PRIMARY KEY,
Nome   VARCHAR(50) NOT NULL,
    Cognome  VARCHAR(50) NOT NULL, 
    Email     VARCHAR(50) NOT NULL,
    RuoloPersona VARCHAR(20) NOT NULL CHECK(RuoloPersona IN ('Studente', 'Professore', 'Tutor', 'Ex-Studente')),
    Telefono NUMERIC(10) NOT NULL,
    Scuola NUMERIC(5) NOT NULL REFERENCES Scuola ON UPDATE CASCADE ON DELETE RESTRICT,
    Genere VARCHAR(50),
    Ruolo VARCHAR(50),
    Materia VARCHAR(50) DEFAULT NULL,
    Classe VARCHAR(5) DEFAULT NULL,
    Sezione VARCHAR(5) DEFAULT NULL,
    Note VARCHAR(100),
    CHECK((RuoloPersona='Professore' AND Materia!=NULL) OR (RuoloPersona!='Professore' AND Materia=NULL)),
    CHECK((RuoloPersona='Studente' AND Classe!=NULL) OR (RuoloPersona!='Studente' AND Classe=NULL)),
    CHECK((RuoloPersona='Studente' AND Sezione!=NULL) OR (RuoloPersona!='Studente' AND Sezione=NULL)));

I am trying to create a table Person that can contain students and teachers too. To make this possible, i've created a field called 'RuoloPersona', that mark if a person is a student or a teacher. I want the field 'Materia' to be NON NULL when a teacher is added and i want the fields 'Classe' and 'Sezione' to be NON NULL when a student is added, in all other cases i want them to be NULL. Obviously what i've written above doesn't work, but explains my idea.

score 3 · Accepted Answer · answered Jun 20 '17 at 10:56

The correct way to express the check constraints is:

CHECK ( (RuoloPersona = 'Professore' AND Materia IS NOT NULL) OR
        (RuoloPersona <> 'Professore' AND Materia IS NULL)
     ),
CHECK( (RuoloPersona = 'Studente' AND Classe IS NOT NULL AND Sezione IS NOT NULL) OR
       (RuoloPersona <> 'Studente' AND Classe IS NULL AND Sezione IS NULL)
     )

This uses the standard SQL operator <> for "not equals". For NULL comparisons, you should always use IS NULL and IS NOT NULL.

Set some field 'NOT NULL' only if a field has a special value

1 Answers1