Question 112780 in Mysql Question posted in
The official MySQL documentation can be found here.

Error in SQL Syntax: Attempting to Initialize Table in MySQL Database

I’m attempting to initialize a table called, "character", in a MySQL server database, and I am getting the SQL syntax error provided below:

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 'character ( character_id INT PRIMARY KEY, first_name VARCHAR(20) NOT NUL' at line 1

My SQL code is as follows:

CREATE TABLE character (
    character_id INT PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    branch VARCHAR(40) NOT NULL,
    game_wins INT(5) DEFAULT 'N/A',
    game_losses INT(5) DEFAULT 'N/A',
    match_wins INT(6) DEFAULT 'N/A',
    match_ties INT(6) DEFAULT 'N/A',
    match_losses INT(6) DEFAULT 'N/A',
    match_no INT(6) DEFAULT 'N/A'
);

I must be missing a syntax error that is straightforward but I’ve looked over this code block several times and I cannot seem to pinpoint the issue.

Tags:

2

Answers


  1. 0

    First of all character is a reserved word witch makes us use “ to denominate it.
    About default value, you cannot set a string "’N/A’" into a INT type.
    Instead I suggest you to use null columns for every INT (in your case) and by default they will be NULL.
    Try it

    CREATE TABLE `character` (
    character_id INT PRIMARY KEY not null,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    branch VARCHAR(40) NOT NULL,
    game_wins INT(5) null,
    game_losses INT(5) null,
    match_wins INT(6) null,
    match_ties INT(6) null,
    match_losses INT(6) null,
    match_no INT(6) null
);

    Let me know if it worked for you

    Login or Signup to reply.
  2. 0

    You are assigning strings (‘N/A’) to INT types. You can use NULL as the default and check for NULL to see if the value has been set. If there’s a better integer value to indicate an invalid value for your situation, you can use that instead of NULL

    CREATE TABLE 'character' (
    character_id INT PRIMARY KEY,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    branch VARCHAR(40) NOT NULL,
    game_wins INT(5) DEFAULT NULL,
    game_losses INT(5) DEFAULT NULL,
    match_wins INT(6) DEFAULT NULL,
    match_ties INT(6) DEFAULT NULL,
    match_losses INT(6) DEFAULT NULL,
    match_no INT(6) DEFAULT NULL
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search