Braydon Rekart Braydon Rekart - 1 month ago 11
MySQL Question

MySQL database schema syntax behaving strangely

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.

Answer

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