title: Inserting multiple choice questions and answers in sql
body: <p>I have two tables, <code>Questions</code> and <code>Answers</code> which store the multiple choice question and the 4 possible answers, respectively. I am trying to insert one question along with its 4 answers at a time.</p>
  
  <pre><code>The INSERT statement conflicted with the FOREIGN KEY constraint .... "dbo.Answers", column 'AnswerID'.
  
  The INSERT statement conflicted with the FOREIGN KEY constraint  table "dbo.Questions", column 'QuestionID'.
  </code></pre>
  
  <p>Here is what my tables look like:</p>
  
  <pre><code>CREATE TABLE [dbo].[Questions] (    
      [QuestionID] INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY,
      [HistID]     INT           NOT NULL,
      [Question]   NCHAR (300)   NOT NULL,
      [AnswerID]   CHAR          NOT NULL,
      FOREIGN KEY (AnswerID) REFERENCES [dbo].Answers (AnswerID),
      FOREIGN KEY (HistID) REFERENCES [dbo].HistoricalEvents (HistID)
  );
  
  CREATE TABLE [dbo].[Answers] (
      [QuestionID] INT         NOT NULL,
      [AnswerID]   CHAR        NOT NULL,
      [Choice]     NCHAR (200) NOT NULL,
      FOREIGN KEY (QuestionID) REFERENCES [dbo].[Questions] (QuestionID), 
      PRIMARY KEY ([AnswerID])
  );
  </code></pre>
  
  <p>These are the inserts I am trying:</p>
  
  <pre><code>INSERT INTO Questions (HistID, Question, AnswerID)
  VALUES (
  2,
  'A major cause of the growth of state and Federal highway systems after World War II was the ___',
  'C'
  )
  
  Declare @QuestionID INT = SCOPE_IDENTITY()
  
  
  INSERT INTO Answers(QuestionID, AnswerID, Choice)
  VALUES 
  (
  @QuestionID,
  'A',
  'increased use of mass transit systems'
  ),
  
  (
  @QuestionID,
  'B',
  'growing prosperity of inner-city areas'
  ),
  
  (
  @QuestionID,
  'C',
  'rapid development of suburbs'
  ),
  
  (
  @QuestionID,
  'D',
  'return of city dwellers to farm areas'
  )
  </code></pre>
  
  <p><strong><em>Expected output:</em></strong>
  I want the above command (or what it should change to) to make the following changes to the tables:</p>
  
  <pre><code>Questions:
  QuestionID   HistID   Question                 AnswerID
  --------------------------------------------------------
      1          2      "A major cause of the...    C     
  
  Answers:
  QuestionID   AnswerID   Choice
  --------------------------------
      1          A         increased use of mass transit systems
      1          B         growing prosperity of inner-city areas
      1          C         rapid development of suburbs
      1          D         return of city dwellers to farm areas
  </code></pre>