Menu
I am trying to create a database to store some questions and answers for a quiz
I have so far two tables:
questions: (Question ID(PK), question string, correct answer ID)
answers: (Answer ID(PK), answer string, question ID)
I'm having trouble setting up the foreign key constraints. Surely I need to make sure that correct answer ID exists in answers and that also question ID in answers exists in the questions table. However, when trying to add these foreign keys in SQliteStudio, I am getting errors which suggest I cannot add a foreign key referencing table A > B when there is already a foreign key constraint that goes from B > A.
860
The FOREIGN KEY constraint is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
If there is exactly one foreign key with the same name as a table in the join, SQL Anywhere uses it to generate the join condition. If there is more than one foreign key with the same name as a table, the join is ambiguous and an error is issued.
If you mean "can foreign key 'refer' to a primary key in the same table?", the answer is a firm yes as some replied.
foreign_keys_columns in SQL. Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables.
This behavior is correct. Otherwise, you'd get a chicken-and-egg problem: you would not be able to insert an answer without inserting a row for the question first, and you also would not be able to insert a question without first inserting a valid answer for it. You will get a similar issue trying to delete a question or an answer being referenced.
A typical solution to this is adding a column is_correct to the answer table.
101
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
