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

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.

CodePudding user response：

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

CodePudding user response：

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
);