Is there an easy way to reference multiple primary keys in one column of destination table?

Question

I apologize for my lack of knowledge or incorrect usage of terms; I am taking an online DBMS course and it is mostly self-taught with Microsoft SQL Server.

We are tasked with creating a database design and inserting data into it for something that interests us. I chose to create a database based on Dungeons and Dragons, and had a question on if I was doing something correctly.

I intend to create a Spell_Source table that will hold the primary key of multiple different tables (Class and Subclass) as one column, and the name of the spell (a primary key in a different table) as the other. When I go to input the data however, the foreign key constraints are stopping the insertion.

I am fully prepared to redesign the database itself if it's a problem in normalization, or if there's a simple fix that I'm missing due the self-taught nature of the class.

Thanks for your help!

CREATE TABLE SPELL_SOURCE (
    SpellName       VarChar(50)     NOT NULL,
    SpellSource     Char(25)        NOT NULL,
    CONSTRAINT      SpellSourcePK1  PRIMARY KEY (SpellName, SpellSource),
    CONSTRAINT      SpellSourceFK   FOREIGN KEY (SpellName)
                        REFERENCES SPELLS(SpellName)
                            ON UPDATE NO ACTION
                            ON DELETE NO ACTION,
    CONSTRAINT      SpellSourceFK1  FOREIGN KEY (SpellSource)
                        REFERENCES CLASS(ClassName)
                            ON UPDATE NO ACTION
                            ON DELETE NO ACTION,
    CONSTRAINT      SpellSourceFK2  FOREIGN KEY (SpellSource)
                        REFERENCES SUBCLASS(SubclassName)
                            ON UPDATE NO ACTION
                            ON DELETE NO ACTION
);

The exact error I'm getting from the Import Data tool is "The INSERT statement conflicted with the FOREIGN KEY constraint "SpellSourceFK1". The conflict occured in database table "dbo.CLASS", "ClassName""

One thing I would suggest is to use names that mean something. FK1, FK2 are nearly useless as you have to constantly refer back to the table definition to know what they are. From the definition of the table you posted that exact error message is not possible. SpellSourceFK1 references the Class table, not the SUBCLASS table. But the error message means you are trying to insert a value in the column that does not exist in the other table. — Sean Lange, Apr 17 '19 at 16:33
Oh dear....I just realized what you are doing. You definitely have a major problem in the design. The way you have this right now means that the same value in SpellSource MUST be in both the CLASS and SUBCLASS tables. Not really sure what you modeling here but something seems a bit off. — Sean Lange, Apr 17 '19 at 16:38
How could the value in SpellSource reference both a class and a subclass at the same time? Sounds like you need to either re-examine your schema design or deal with this non-standard design using triggers instead of constraints. — Aaron Bertrand, Apr 17 '19 at 16:39
Thanks for the comments so far. I am trying to use the "SpellSource" column as a catch-all to reference several different tables. Based on the information so far, I would gather that this isn't a possibility? — Ryan Gress, Apr 17 '19 at 16:42
I'm guessing that the SpellSource can be either a class or a subclass. In this case, you could create a subclass that would refer to the whole class and just create a foreign key to SubClass. If more tables for source are needed, you might need a larger redesign. — Luis Cazares, Apr 17 '19 at 16:44
Additionally, I made a mistake when posting the error message. I had attempted switching the order of the constraints to fix the issue, so the error message posted is before I ordered the Subclass and Class constraints differently — Ryan Gress, Apr 17 '19 at 16:44
@LuisCazares That's a good thought, but unfortunately, due to the data in the Class vs. Subclass tables, that isn't a possibility. You are correct, though, that I am trying to have SpellSource be either a class or subclass. Do you have any ideas on how to redesign the structure? — Ryan Gress, Apr 17 '19 at 16:48
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Apr 17 '19 at 18:23
(Obviously) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 17 '19 at 18:24
A FK says values appear elsewhere. Your constraint is not a FK & it is not 3 FKs. You do not need to keep data about what is a subclass of what superclass because that does not change. You just need a FK from the ids in a table with subclass data to the ids in the corresponding table with superclass data. Database subtyping/inheritance is a faq. PS The constraint of a value having to appear in one of multiple other places is sometimes called a distributed FK. But even it were supported/easy, you don't want one. The question design is a common anti-pattern for subtyping. — philipxy, Apr 17 '19 at 18:42

Olivier Jacot-Descombes · Accepted Answer · 2019-04-21T19:30:19.297

1

The problem is that you entered a value for SpellSource that has no corresponding ClassName in table CLASS. A column can be part of different Foreign Keys if those are different compound keys. E.g. FK (a, b), FK (b, c) where b belongs to 2 FKs. But otherwise a single column (as a rule of thumb) should have only one FK.

Also, if you have classes and subclasses, you would only reference the subclass here and create a 1 to n relationship between class and subclass. I.e., subclass would have a foreign key for ClassID

 SPELL_SOURCE                 SPELLS
+-------------  ---+         +---------------+
| PK FK SpellID    | o-----> | PK SpellID    |
| PK FK SubclassID | o--+    |    SpellName  |
+------------------+    |    +---------------+
                        |
                        |     SUBCLASS                    CLASS
                        |    +-----------------+         +---------------+
                        +--> | PK SubclassID   |    +--> | PK ClassID    |
                             |    SubclassName |    |    |    ClassName  |
                             | FK ClassID      | O--+    +---------------+
                             +-----------------+

Don't use the names as PK. It will make it very difficult to change names later. Instead only reference an int IDENTITY(1,1) (auto incrementing) primary key that never changes and store the name in a separate column that you can edit at any time. See: CREATE TABLE (Transact-SQL) IDENTITY (Property).

You can query the combined information with

SELECT
    SS.SpellID, SS.SubclassID,
    S.SpellName,
    C.ClassName,
    SC.SubclassName, SC.ClassID
FROM
    SPELL_SOURCE SS
    INNER JOIN SPELLS S
        ON SS.SpellID = S.SpellID
    INNER JOIN SUBCLASS SC
        ON SS.SubclassID = SC.SubclassID
    INNER JOIN CLASS C
        ON SC.ClassID = C.ClassID
ORDER BY
    C.ClassName, SC.SubclassName, S.SpellName

But note that with this design, the same spell could belong to different classes and subclasses. If a spell can belong to only one subclass, then the structure should look like.

Class   1 --> n   Subclass   1 --> n   Spell

According to your comments a spell could belong to a class instead of a subclass (and indirectly to a class also). Then I would suggest the following structure

 SPELL_SOURCE (separate PK because of nullables, Unique Constraint UC instead)
+------------------+              SPELLS
| PK SpellSourceID |             +---------------+
| FK UC SpellID    | o---------> | PK SpellID    |
| FK UC SubclassID | o------+    |    SpellName  |
| FK UC ClassID    | o--+   |    +---------------+
+------------------+    |   |
                        |   |     SUBCLASS                       CLASS
                        |   |    +-----------------+    +-----> +---------------+
                        |   +--> | PK SubclassID   |    |  +--> | PK ClassID    |
                        |        |    SubclassName |    |  |    |    ClassName  |
                        |        | FK ClassID      | o--+  |    +---------------+
                        |        +-----------------+       |
                        |                                  |
                        +----------------------------------+

Where in SPELL_SOURCE both ClassID and SubclassID are NULLABLE. Always only one of the two would be not null. You could add a CHECK constraint (ClassID IS NULL AND SubclassID IS NOT NULL) OR (ClassID IS NOT NULL AND SubclassID IS NULL). And use LEFT JOINS in the query.

See: http://www.sqlfiddle.com/#!18/107dd/3/0

Yet another approach would be to keep the first structure but to have a main or default subclass entry in each class. E.g., a subclass having a SubclassName = NULL. This entry would be representative of the class.

For drop-down-lists you could select the entries like this

SELECT
    S.SubclassID,
    CASE WHEN S.SubclassName IS NULL THEN
        'CLASS: ' + C.ClassName
    ELSE
        S.SubclassName + ' (' + C.ClassName + ')'
    END AS Name
FROM
    CLASS C
    INNER JOIN SUBCLASS S
        ON C.ClassID = S.ClassID
ORDER BY
    C.ClassName, 
    CASE WHEN S.SubclassName IS NULL THEN 0 ELSE 1 END,
    S.SubclassName

See: http://www.sqlfiddle.com/#!18/d8777/1/0

edited Apr 21 '19 at 19:30

answered Apr 17 '19 at 16:50

Olivier Jacot-Descombes

104,806
13
138
188

The only problem with this solution is that, with the design of this database, I'm trying to set the SpellSource to accept both ClassName _and_ SubclassName, as there are spells that only correspond to classes, and likewise to subclasses. – Ryan Gress Apr 17 '19 at 17:25
Then you will need three columns SPELL_SOURCE(SpellID, SubclassID, ClassID) all of them PK and FK. In my design I assumed that a subclass can only belong to one class, i.e., that you have a strict class/subclass hierarchy (what the names suggest). Then the spell_source is indirectly assigned a class through a subclass. – Olivier Jacot-Descombes Apr 17 '19 at 17:28
I apologize for the confusion; this is the case. Each subclass must belong to 1 and only 1 class. However, I am trying to make it possible that SubclassName or ClassName can be the 'source' of the spell. Would this solution be much easier to solve if that is the case? Additionally, Spells can belong to multiple different classes and subclasses – Ryan Gress Apr 17 '19 at 17:33
I was under the impression that a PK could _not_ be nullable. Is this only the case in tables with a single PK? – Ryan Gress Apr 17 '19 at 17:58
@RyanGress & OlivierJacot-Descombes Data about what is a subclass of what superclass is not needed because that does not change. See my comments on the question. – philipxy Apr 17 '19 at 18:37
@RyanGress: Yes, of course you are right. In this case you will have to make a PK which is independent of the FKs and instead make a unique constraint on the FKs. – Olivier Jacot-Descombes Apr 17 '19 at 20:02
@OlivierJacot-Descombes So you're saying I should make an entirely different PK (perhaps an auto-incrementing int, as before) and leave the 3 columns of SpellID, ClassID, and SubclassID as just FKs? – Ryan Gress Apr 17 '19 at 21:20
Yes. If you want the FKs to be unique, create a UNIQUE constraint. Those accept NULL columns. Unless you choose the variant with the empty subclass (last section of my answer) where each class would have a subclass representing the class, so that you could always select a subclass. – Olivier Jacot-Descombes Apr 17 '19 at 21:27
I'm here to ask: what did you use to generate those sweet ASCII art ERDs‽ – Ben Thul Apr 18 '19 at 04:26
With the online tool [ASCIIFlow](http://asciiflow.com/). But then I downloaded the raw design to refine it in a "normal" editor. – Olivier Jacot-Descombes Apr 18 '19 at 12:28
@OlivierJacot-Descombes Thanks for you help yesterday. I was just now able to create the table as referenced in the diagram from your answer. However, I face two problems: 1) I can't set any column as unique (apart from primary) due to the nature of the data. 2) I can't set any of the columns as FK, nor can I add the nullCheck constraint specified. – Ryan Gress Apr 19 '19 at 18:26
1) The idea (in the second diagram) is to create a single compound unique key consisting of `SpellID`, `SubclassID` and `ClassID`, since no column alone is unique. 2) I'm not sure why. A FK has always to point to a PK in the other table ans you will need 3 separate FKs. Why can you not add the check constraint? – Olivier Jacot-Descombes Apr 20 '19 at 15:05
"But otherwise a single column should have only one FK." is wrong. – philipxy Apr 21 '19 at 18:28
@philipxy: Can you provide a good example for a column having more than one FK. – Olivier Jacot-Descombes Apr 21 '19 at 18:34
Your comment misses the point. There's no such restriction either in the language or in sensible design. A FK constraint says subrow values appear elsewhere. When that is so we should declare it to the DBMS unless either it is redundant because it is implied by other declarations we chose or it forms a directed cycle with other declarations we chose that a DBMS limitation cannot handle. (Such cycles have nothing to do per se with 2 FKs sharing referencing columns.) I would agree that if we don't have a case of a subrow also in 2 other tables--like here--then we shouldn't wrongly declare we do. – philipxy Apr 21 '19 at 19:07
I did not say "cannot", I said "should not". My statement is to be understood as a rule of thumb. I've seen many database designs, but never saw a column having 2 non-compound FKs. – Olivier Jacot-Descombes Apr 21 '19 at 19:29
It is not a rule of thumb. It doesn't matter what you have or haven't seen. A FK is for something & it's used it for that. The only choice is which to declare & which not when there are choices under transitivity & circularity. PS What are you actually claiming? Why do you say & what you mean by "shouldn't" declare? If never, *why*? If not never, you haven't *given* a *rule*, you've give a likelyhood--*when does* one so declare? I have no idea what distinction you see between cannot & should not in design heuristics. If one shouldn't, it won't--so can't--arise. – philipxy Apr 22 '19 at 07:18
I just don't see any advantage in having the same column have an FK declared on it pointing to a table A and at the same time have another FK pointing to table B. It would mean that this column could only have values matching PKs of A and B at the same time. There might be rare cases where this desired; however, if you teach a beginner to have two FK on his columns, then you are most probably pointing him into a wrong direction. After all, this the cause of his problem. – Olivier Jacot-Descombes Apr 22 '19 at 14:14
@OlivierJacot-Descombes I just wanted to thank you again for the continued support on this question. I've finally gotten it all to work by scanning through the data I had been trying to insert. One nulled column made the entire thing not work, but I finally found the bug. Thanks again. – Ryan Gress Apr 22 '19 at 15:03

score 1 · Answer 2 · answered Apr 17 '19 at 17:20

1

Just wanted to throw my support behind the answer from @Olivier's response. You're going to cause yourself a lot of grief attempting to move forward with your current design.

However by creating a 1 -> N relationship between your SubClass and your Class you will always maintain referential integrity, and your Class will easily be queried through the sub-class

answered Apr 17 '19 at 17:20

Jim Daily

23
4

You are right. But as far as I understand @RyanGress wants also be able to assign a class to a spell without assigning it a subclass in cases where a spell is not that specific. (At first I did not understand that either.) – Olivier Jacot-Descombes Apr 17 '19 at 20:08

Is there an easy way to reference multiple primary keys in one column of destination table?

2 Answers2