MySQL database schema syntax behaving strangely

Question

I'm trying to write a MySQL database (called security_db.sql) in which I outline some tables about a security guard job. The code itself is below. Sorry about the weird formatting.

drop database if exists security;

create database security;

use security;


drop table if exists caller;

drop table if exists number;

drop table if exists officer;

drop table if exists suspect;

drop table if exists report;

drop table if exists car;

drop table if exists writeup;

drop table if exists activitylog;

drop table if exists ticket;


create table caller

(

firstname varchar(64),

lastname varchar(64),

cellnumber int unsigned not null primary key,

location varchar(64),

);

insert into caller (firstname, lastname, cellnumber, location)

values (`Braydon`, `Rekart`, `2174088935`, `Stoddard`);

create table report

(

incidentreport varchar(64) not null primary key,

);
insert into report (incidentreport) values (`Testing this database.`);

reate table activitylog

(

report text not null,

date int not null primary key,

location varchar(64) not null,

);

insert into activitylog (report, date, location) values (`Testing this 

database`, `112233`, `OlinMahan`);

create table number

(

securityphone int not null primary key,

);

insert into number (securityphone) values (`1217666666`);

create table suspect

(
suspectfirstname varchar(64) not null,

suspectlastname varchar(64) not null,

weight int,

height int,

suspectid int not null primary key,

);

insert into suspect (suspectfirstname, suspectlastname, suspectid)

values (`Braydon`, `Rekart`, `3`);

create table writeup

(
recipientfirstname varchar(64) not null,

recipientlastname varchar(64) not null,

whogaveit varchar(64) not null,

reason text not null,

writeupid int not null primary key,

reason text not null,

writeupid int not null primary key,

);

insert into writeup (recipientfirstname, recipientlastname,

whogaveit, reason, writeupid) values (`Braydon`, `Rekart`, `Karson`, `Late`,

 `45`);

create table officer

(

officerid int not null primary key,

position varchar(64) not null,

firstname varchar(64) not null,

lastname varchar(64) not null,

);

insert into officer (officerid, position, firstname, lastname)

values (`1`, `Crewhead`, `Braydon`, `Rekart`);

create table car

(

make varchar(64),

color varchar(64),

model varchar(64),

licenseplatenumber varchar not null primary key,

);

insert into car (licenseplatenumber) values (`N33D4SP33D`);

create table ticket
(
ticketnumber int primary key,

writerfirstname varchar(64) not null,

writerlastname varchar(64) not null,

recipientfirstname varchar(64) not null,

recipientlastname varchar(64) not null,

recipientfirstname varchar(64) not null,

recipientlastname varchar(64) not null,

writerid int,
);

insert into ticket (ticketnumber, writerfirstname, writerlastname, 
recipientfirstname, recipientlastname)

values (`3`, `Braydon`, `Rekart`, `Karson`, `Gragert`);

The errors it gives me are as follows.

mysql> use security

Database changed

mysql> source security.db

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): 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 ')' at line 7

ERROR 1146 (42S02): Table 'security.caller' doesn't exist

ERROR 1064 (42000): 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 ')' at line 4

ERROR 1146 (42S02): Table 'security.report' doesn't exist

ERROR 1064 (42000): 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 ')' at line 6

ERROR 1146 (42S02): Table 'security.activitylog' doesn't exist

ERROR 1064 (42000): 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 ')' at line 4

ERROR 1146 (42S02): Table 'security.number' doesn't exist

ERROR 1064 (42000): 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 ')' at line 8

ERROR 1146 (42S02): Table 'security.suspect' doesn't exist

ERROR 1064 (42000): 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 ')' at line 8

ERROR 1146 (42S02): Table 'security.writeup' doesn't exist

ERROR 1064 (42000): 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 ')' at line 7

ERROR 1146 (42S02): Table 'security.officer' doesn't exist

ERROR 1064 (42000): 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 'not null primary key, )' at line 6

ERROR 1146 (42S02): Table 'security.car' doesn't exist ERROR 1064 (42000): 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 ')' at line 9

ERROR 1146 (42S02): Table 'security.ticket' doesn't exist

No matter how I fiddle around with the syntax I can't seem to figure it out. I feel like it's a total rookie mistake but I need help. Thank you.

You have an extra comma after the last field definition in every single create table statement, therefore none of the tables are created and none of the insert can succeed. You also use backticks around the values to be inserted incorrectly. Backticks are used to enclose object identifiers, such as table or field names. String values should be enclosed by single or double quotes, numeric values should not be enclosed. — Shadow, Oct 28 '16 at 01:58
It just seems weird to me that you can do this --> location varchar(64), ); The last comma after the last field in a table definition. — Ross Bush, Oct 28 '16 at 02:01
You're missing the `c` at the beginning of `reate table activitylog`. Is that in the real code or a copying error? — Barmar, Oct 28 '16 at 02:35

score 0 · Accepted Answer · answered Oct 28 '16 at 02:04

0

You have an extra comma after the last field definition in every single create table statement, therefore none of the tables are created and none of the insert can succeed.

Example:

...
location varchar(64) not null, <- this comma

);

You also use backticks around the values to be inserted incorrectly. Backticks are used to enclose object identifiers, such as table or field names. String values should be enclosed by single or double quotes, numeric values should not be enclosed.

Example:

insert into caller (firstname, lastname, cellnumber, location)
values ('Braydon', 'Rekart', 2174088935, 'Stoddard');

answered Oct 28 '16 at 02:04

Shadow

33,525
10
51
64

Ohhh. Thank you! I'm just learning how syntax and stuff works right now as you can see. – Braydon Rekart Oct 28 '16 at 02:22
@BraydonRekart See http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks regarding the quotes and backticks. – Barmar Oct 28 '16 at 02:37
If the above reply answered your question, then pls mark the reply as an answer to indicate other readers that the issue has been resolved! – Shadow Oct 28 '16 at 03:04
A small issue. I went through and removed all of the last commas inside the code, and also changed the backticks to single quotes. I still get these errors though. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio n for the right syntax to use near 'not null primary key )' at line 6 ERROR 1146 (42S02): Table 'security.car' doesn't exist When going back to the code in question it seems fine. – Braydon Rekart Oct 28 '16 at 03:12
Well, check out the statement causing this error just before the word `not` and you are very likely to find the reason for this error. Hint: check out how you declared varchar fields in other cases. – Shadow Oct 28 '16 at 05:26

MySQL database schema syntax behaving strangely

1 Answers1