Syntax Error while creating a table in SQL

Question

I am trying this query and despite several attempts, I am still getting a syntax error when I create Table Call. The other tables get created just fine.. I don't understand why

Code for Student

  CREATE TABLE Student (
      `student_id` int NOT NULL AUTO_INCREMENT,
      `phone_number` varchar(50) NOT NULL,
      `name` varchar(50) NOT NULL,
      `school` varchar(50) NOT NULL,
      `class` varchar(50) NOT NULL,

      PRIMARY KEY (`student_id`)
    )

Table Stories

    CREATE TABLE Stories (
      `story_id` int NOT NULL AUTO_INCREMENT,
      `story_name` varchar(50) NOT NULL,
      `number_questions` int NOT NULL,
      `file_name` varchar(100) NOT NULL,

      PRIMARY KEY (`story_id`)
    )

Table Questions

    CREATE TABLE Questions (
      `question_id` int NOT NULL,
      `story_id` int NOT NULL,
      `file_name` varchar(100) NOT NULL,
      `concept_tested` varchar(100) NOT NULL,
      `difficuly` varchar(50) NOT NULL,
      `number_options` int NOT NULL,
      `correct_answer` int NOT NULL,
      `call_number` int NOT NULL,

      PRIMARY KEY (`question_id`,`story_id`),

      FOREIGN KEY (`story_id`)
      REFERENCES `Stories`(`story_id`)
      ON DELETE CASCADE
    )

Table Call

    CREATE TABLE Call (
      `call_id` int NOT NULL AUTO_INCREMENT,
      `student_id` int NOT NULL,
      `story_id` int NOT NULL,
      `question_id` int NOT NULL,
      `call_number` int NOT NULL,
      `total_number` int NOT NULL,

      PRIMARY KEY (`call_id`),

      FOREIGN KEY (`student_id`)
      REFERENCES `Student`(`story_id`)
      ON DELETE CASCADE,

      FOREIGN KEY (`story_id`)
      REFERENCES `Stories`(`student_id`)
      ON DELETE CASCADE,

      FOREIGN KEY (`question_id`)
      REFERENCES `Stories`(`question_id`)
      ON DELETE CASCADE
    )

Here is my error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Call ( `call_id` int NOT NULL AUTO_INCREMENT, `student_id` int NOT NULL, ' at line 1

I have tried editing my code and checking it several times but the problem remains unsolved

I solved the problem,

It is:

CREATE TABLE `Call` (
  `call_id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `story_id` int NOT NULL,
  `question_id` int NOT NULL,
  `call_number` int NOT NULL,
  `total_number` int NOT NULL,

  PRIMARY KEY (`call_id`),

  FOREIGN KEY (`student_id`)
  REFERENCES `Student`(`student_id`)
  ON DELETE CASCADE,

  FOREIGN KEY (`story_id`)
  REFERENCES `Stories`(`story_id`)
  ON DELETE CASCADE,

  FOREIGN KEY (`question_id`)
  REFERENCES `Questions`(`question_id`)
  ON DELETE CASCADE
)

Is it mysql **or** sql-server? Cannot be both at the same time... And update the question with error you're getting. *SYNTAX ERROR* is not the whole thing you're getting. — MarcinJuraszek, Feb 15 '14 at 05:12
those tick marks for mysql.. not sql server. i think the op was just confused. — sam yi, Feb 15 '14 at 05:18
@MarcinJuraszek I have edited to add my error, do you have suggestions? — gazubi, Feb 15 '14 at 05:20
Your errormessage doesn't not match with the given statement! There is a table name "Call" but not in your script? — devanand, Feb 15 '14 at 05:32
@bob_d Why are you changing tags to `sql-server` when you admin, that you use `innoDB`, which means you use `mysql`? — MarcinJuraszek, Feb 15 '14 at 05:56

score 0 · Answer 1 · edited May 23 '17 at 11:49

I found that these conditions must be satisfied to not get error 150:

The two tables must be ENGINE=InnoDB. (can be others: ENGINE=MyISAM works too)
The two tables must have the same charset.
The PK column(s) in the parent table and the FK column(s) must be the same data type.
The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.
And the child table cannot be a temporary table.

Hope this helps.

Try like this

CREATE TABLE Questions (
  `call_id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `story_id` int NOT NULL,
  `question_id` int NOT NULL,
  `call_number` int NOT NULL,
  `total_number` int NOT NULL,

  PRIMARY KEY (`call_id`),

  FOREIGN KEY (`student_id`)
  REFERENCES `Student`(`story_id`)
  ON DELETE CASCADE,

  FOREIGN KEY (`story_id`)
  REFERENCES `Stories`(`student_id`)
  ON DELETE CASCADE,

  FOREIGN KEY (`question_id`)
  REFERENCES `Stories`(`question_id`)
  ON DELETE CASCADE
)ENGINE=MYISAM CHARACTER SET UTF8;

FIDDLE DEMO

Take a look at here

Syntax Error while creating a table in SQL

1 Answers1

FIDDLE DEMO