I need a constraint that allows only the values NULL or a valid DNI (from Spain) to be inserted. I do not know how to add the one that is also worth the null value, I leave my constraint to see if they can finish it.
Thanks.
ALTER TABLE CITAS ADD CONSTRAINT CK_DNI_CITAS CHECK (REGEXP_LIKE(
DNI_PACIENTE,'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'));
You need to set the condition of NULL
ALTER TABLE CITAS ADD CONSTRAINT CK_DNI_CITAS CHECK (
REGEXP_LIKE(DNI_PACIENTE,'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]') OR DNI_PACIENTE IS NULL
);
You can also write the regular expression more compactly:
ALTER TABLE CITAS ADD CONSTRAINT CK_DNI_CITAS CHECK (
REGEXP_LIKE(DNI_PACIENTE,'\d{8}[A-Z]') OR DNI_PACIENTE IS NULL
);
Or you can even solve everything as a regular expression:
ALTER TABLE CITAS ADD CONSTRAINT CK_DNI_CITAS CHECK (
REGEXP_LIKE(NVL(DNI_PACIENTE, 'NULL'),'\d{8}[A-Z]|NULL')
);