|
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 |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 29 May '17, 13:47
question was seen: 2,400 times
last updated: 31 May '17, 08:20
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 );
Funny, was just about to write the same...
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
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).
The MATCH clause helps to differ between simple and full foreign key matches, cf. the docs and that FAQ.
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.
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.