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

asked 29 May, 13:47

robert's gravatar image

robert
578283548
accept rate: 0%

1

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 );

(29 May, 15:17) Chris Keating
Replies hidden
1

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

(29 May, 15:18) Volker Barth

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

(30 May, 03:18) robert
Replies hidden
2

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).

(30 May, 04:10) Breck Carter
1

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

(30 May, 06:23) Volker Barth

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.

(31 May, 08:17) robert

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.

(31 May, 08:20) robert
showing 4 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×9

question asked: 29 May, 13:47

question was seen: 175 times

last updated: 31 May, 08:20