-1

I have two tables in a MySQL 5.7.x DB. Table A, whose data I maintain, and table B, which I can only read. I would like to define a relationship (somewhat like a foreign key) from a column in table A to refer to values in a column in table B. The exception is that the referenced column in table B does not always contain the value to which the column in table A refers.

I don't mind that B's column doesn't have the value. As long as the relationship is defined for advisory purposes, it's OK. However, if I try to use a foreign key relationship for this purpose, it requires the value from A to be in B, too.

How can I define the relationship without the requirement? That is, can I let it be optional?

I'm looking for this to be specifically in the direction of A referring to a value in B, but B doesn't contain that value, and that's OK. Not as one person suggested, the column in A is null, not pointing to any value in B.

I've searched for an answer to this online, but I can't find the solution. Maybe I'm not phrasing the search terms well or I'm using the wrong terminology.

I think this may be possible with MySQL 8's NOT ENFORCED option, but that's only possible with CHECK type of constraints.

Mr. Lance E Sloan
  • 3,297
  • 5
  • 35
  • 50
  • 2
    Remove the foreign key constraint. It seems you only want something like "soft FKs" that are not really enforced... – The Impaler Jun 11 '20 at 13:29
  • 'can I let it be optional?' - nope – P.Salmon Jun 11 '20 at 13:30
  • 1
    Columns can be `NULL`, including your "FK" column. Is that an option for you? – Progman Jun 11 '20 at 13:33
  • 2
    "I would like to define a foreign key relationship" No, you wouldn't, because what you want isn't a FK constraint. You clearly suspect that since you can't get it to work. So you need to explain exactly what you want without (mis)using the term "FK". Have you researched SQL FKs? Do you understand that if you set the referencing value to NULL the constraint is satisfied? This is going to be a faq. But to search you need to clearly concisely state what you want. "be missing", "optional", "defined for advisory purposes" etc are not clear. Also in a longer version consider a concrete example. – philipxy Jun 11 '20 at 13:50
  • Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Jun 11 '20 at 13:50
  • @philipxy, I've made some edits to avoid the term "FK". If you have other suggestions, please tell me. I'll work on further edits, like a concrete example. Thanks for the feedback so far. – Mr. Lance E Sloan Jun 11 '20 at 15:25
  • That is an improvement. But "reference" is what a FK does & otherwise is vague. Also a table represents a relation(ship)/association, FKs are wrongly called "relationships". But you say "relationship" when you mean "constraint". If you mean that you want a subrow for some columns in a table to appear elsewhere, say that. Etc. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. – philipxy Jun 11 '20 at 22:41
  • What you seem to be looking for may be a "comment". Except it's not clear why you need to document that in code. On the other hand if you want the constraint to hold when/iff some other condition holds or doesn't hold, eg when a row is or isn/t the "most recent", possibly distilled to or remembered by some boolean column, then that is a constraint that you likely can & should express in code (via declaration or trigger). But you don't describe anything about that. Take the time to very clearly fully say what you mean, don't try to cram many words in to 1 or phrases into 1 or sentences into 1. – philipxy Jun 11 '20 at 22:52

1 Answers1

1

Foreign keys in MySQL can be enforced or not at session level (something really unorthodox and IMHO a design mistake):

SET FOREIGN_KEY_CHECKS = 0;
-- Do stuff
SET FOREIGN_KEY_CHECKS = 1;

But it's all-or-nothing. Advisory foreign keys is not really a concept in relational databases design. At first sight, I can't see what problem it would solve.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • As a side note, this could be used as "the poor man's deferrable constraints" that are not implemented in MySQL... – The Impaler Jun 11 '20 at 13:35
  • Thanks for the suggestion, but that's not what I'm looking for. I'm looking for an unenforced FK for a specific column, not limited to a session. – Mr. Lance E Sloan Jun 11 '20 at 15:08
  • I wouldn't call it suggestion. Please don't. It's possible that your use case has another solution but the feature you're explicitly asking for doesn't work that way. – Álvaro González Jun 11 '20 at 15:26