Just an observation: It seems odd that foreign keys cannot be references to unconstrained superkeys...*
-
"Academic" description first, then a specific example.
Given two tables, A and B, where...
* k is defined as the primary key of A
* c is any other column of A
* k is a foregin key in B to A
* c is is any other column of Bit seems a bit odd that it is not legal in SQL to define a foreign key from B (k, c) references A(k, c) even though {k, c} is a superkey of A. That is to say, it can be done but an additional unique constraint must be added to A on (k, c) first, which is logically unnecessary given that k is already defined as a key.
This isn't something that shows up very often, but it showed up today while I was sketching out a data model. I'll describe a hypothetical equivalent situation by way of example, DDL included at the end.
Imagine we want to build a model for a "questionnaire" system.
A questionnaire may contain many questions, and questions can be reused across different questionnaires.
When a questionnaire is filled out (which I will call an "execution" because I can't think of anything better righ tnow), we track some "header" information (maybe the person who filled it out, the date of completion, comments they had about the questionnaire as a whole, and so on) as well as the answers to each question.The "execution_answers" table has a foreign key to the questionnaire-to-question mapping table, because obviously it's only legitimate to provide answers to questions that were on the questionnaire being filled out!
But it also seems natural that the "execution header" itself references the questionnaire, because "that's the questionnaire we were executing". So there's also a relationship from the execution header to the questionnaire header.
But this means there's a potential contradiction in the model: It is possible to put rows into the "execution answers" table which indicate a different questionnaire than the one that the "execution header" for those answers represents!
For instance, we might say that "execution 1" was for "questionnaire 1", but "answer 1 of execution 1" was for "question 3 on questionnaire 2", which is a contradiction.
The way to prevent this would be to create a foreign key from execution_answers to executions on the combination of (execution_id, questionnaire_name) instead of on the single column (execution_id). This is logically fine, because execution_id is a key for the executions table, so the combination of (execution_id, questionnaire_name) is a superkey, ie, also guaranteed to be unique by existing structure.
But SQL won't actually allow this per se. Instead, we must first create a logically redundant unique constraint on Executions table across the two columns (execution_id, questionnaire_name)
-- the questionnaire "header"
create table Questionnaires (
questionnaire_name char primary key
)
go-- a list of questions that can be used across different questionnaires
create table Questions (
question_id int primary key,
question_text char
)
go-- a mapping of questions to questionnaires
create table Questionnaire_question_usage (
questionnaire_name char foreign key references Questionnaires,
question_id int foreign key references Questions,
constraint PK_qqu primary key (questionnaire_name, question_id)
)
go-- create a row here each time a questionnaire is filled out
create table Executions (
execution_id int primary key,
questionnaire_name char foreign key references Questionnaires,
execution_date date
)
go-- the answers given on a particular execution
create table Execution_answers (
execution_id int foreign key references Executions,
questionnaire_name char,
question_id int,
answer_text char
constraint fk_x_qqu foreign key (questionnaire_name, question_id) references Questionnaire_question_usage(questionnaire_name, question_id)
)
go -
I must agree with SQL Server on this one 😉 that schema is too ambiguous to constrain properly.
😎Here is a quick suggestion of a better way of doing this
-
Further on the schema I posted previously
Questionnaire
An instance of a collection of questions and their properties.Question
Individual unique questions and their properties.Questionnaire_question
Container for the instances of questions belonging to each questionnaire.- Unique constraint on questionnaire-question prevents duplicate questions within a questionnaire.
- Foreign key constraint on Questionnaire prevents orphan questions.
- Foreign key (non null-able) prevents empty questionnaires.
Execution
A header for any instances of collection of answers for any questionnaire.- Foreign key constraint to the Questionnaire_question forces the previously listed constraints to be applied to any set of answers.
Execution_answer
Container for the answers belonging to each execution.- Foreign key constraint to Execution constraints the answer to a specific questionnaire-execution instance
- Foreign key constraint to Questionnaire_question constraints it to a specific question within the previously constrained Questionnaire-execution collection.
-
That's the same schema and suffers the same problem: It is possible for an execution answer to specify a questionnaire that is different from its execution header.
-
Those two schemas are not the same
😎Original schema
+--------------------+
| Execution |
+--------------------+
+--------------------+ | Execution_id |-+-----------,
| Questionnaire | ,--+<| questionnaire_name | |
|--------------------| | | Execution_date | | +--------------------+
| Questionnaire_name |-+---| +--------------------+ | | Execution_answer |
+--------------------+ | | +--------------------+
| +------------------------------+ '------+<| Execution_id |
+---------------+ | | Questionnaire_question_usage | ,------+<| questionnaire_name |
| Question | | +------------------------------+ | ,----+<| question_id |
+---------------+ '--+<| Questionnaire_name |>+-' | | answer_text |
| Question_id |-+-----------+<| Question_id |>+---' +--------------------+
| Question_text | +------------------------------+
+---------------+My suggestion
,--------------------------------,
+--------------------+ +---------------------------+ | +------------------+ | +---------------------------+
| Questionnaire | | Questionnaire_question | | | Execution | | | Execution_answer |
|--------------------| +---------------------------+ | +------------------+ | +---------------------------+
| Questionnaire_id |-+---, | Questionnaire_question_id |>+--' | Execution_id |-+--, '--+<| Questionnaire_question_id |
| Questionnaire_name | '---+<| Questionnaire_id |>+-------+<| Questionnaire_id | '----+<| Execution_id |
+--------------------+ ,---+<| Question_id | | Execution_date | | Answer_text |
| | Question_ordinal | +------------------+ +---------------------------+
+---------------+ | +---------------------------+
| Question | |
+---------------+ |
| Question_id |-+--------'
| Question_text |
+---------------+?**
-
They're not identical, but they are the same in terms of the problem being addressed here. Ie, the second one is left with the same problems. Well, actually the problem does not exist in the first one, as long as we create that additional unique constraint as I described. But in the second schema there is no way to declaratively defend against the issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply