Constraint which doesn't allow NULL values (or empty string) in fk column

[huber1]

Hi

I have, for example, the following constraint:

ALTER TABLE hades.persons_roles ADD CONSTRAINT persons_role_precised_by_fk FOREIGN KEY ( role_supplementary_name, role_supplementary_role_id ) REFERENCES hades.role_supplementaries ( name, role_id );

The column role_supplementary_name contains valid names, the column role_supplementary_role_id contains currently some NULL values. Although reading the DOCX, I can't seem the find the correct notation for NOT allowing NULL values in this constraint. Any hint?

Thanks and regards, Robert

[chris_keating]

If I understand your question, you need to add NOT NULL before FOREIGN KEY as in:

ALTER TABLE hades.persons_roles ADD CONSTRAINT persons_role_precised_by_fk NOT NULL FOREIGN KEY ( role_supplementary_name, role_supplementary_role_id ) REFERENCES hades.role_supplementaries ( name, role_id );

[VolkerBarth]

Funny, was just about to write the same...

[huber1]

Thanks Chris, and Volker for the answer. Am I correct that an optional constraint based on multiple columns (like the above) is comparing column by column, i. e. it checks if the value in role_supplementary_name exists in the name, and then it checks if the value in role_supplementary_role_id is to be found in role_id? That means a situation having one column filled with a valid value and the other column having NULL or an empty string leads to a valid constraint?

Regards, Robert

[Breck_Carter]

To answer one small part of your questions: Do not say "NULL values (or empty string)" as if they are the same thing. An empty string is a valid non-null value... SQL Anywhere is not the same as Oracle (where the empty string is sometimes maybe treated as NULL or a single space).

[VolkerBarth]

The MATCH clause helps to differ between simple and full foreign key matches, cf. the docs and that FAQ.

[huber1]

Breck, you are absolutely correct in pointing for the difference between NULL and empty string. I completely agree with you and was too lazy to mention both in the way I did. Thanks also for the link to the thread.

[huber1]

Volker, the mentioning of simple and full foreign key is very helpful, was not aware of it and thought, it's always the full method. Lesson learned is to verify.