CODEWITHSUNDEEP
mysql
Deliri
Deliri

Reputation: 126

Adding a table to MySQL keeps throwing up errors

Following a tutorial for web development. Here's the SQL code 

CREATE TABLE 'users_relationships' (
'users_relationship_id' INT(10) NOT NULL,
'from_user_id' INT(10) NOT NULL,
'to_user_id' INT(10) unsigned NOT NULL,
'users_relationship_type' VARCHAR(10) NOT NULL,
'users_relationship_timestamp' DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ('users_relationship_id'),
INDEX 'from_user_id' ('from_user_id'),
INDEX 'to_user_id' ('to_user_id'),
INDEX 'from_user_id_to_user_id' ('from_user_id', 'to_user_id'),
INDEX 'from_user_id_to_user_id_users_relationship_type' ('from_user_id', 'to_user_id', 'users_relationship_type'));

Whenever I run it i get the following error;

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 ''users_relationships' ( 'users_relationship_id' INT(10) NOT NULL, 'from_' at line 1

I tried multiple changes but it keeps giving me the same error. The code is directly from the book. Any ideas?

Upvotes: 0

Views: 48

Answers (3)

Drew
Drew

Reputation: 24949

You don't need backticks or single quotes. Just strain on the body. Single quotes however are downright syntax errors waiting to happen.

CREATE TABLE users_relationships (
users_relationship_id INT(10) NOT NULL,
from_user_id INT(10) NOT NULL,
to_user_id INT(10) unsigned NOT NULL,
users_relationship_type VARCHAR(10) NOT NULL,
users_relationship_timestamp DATETIME not null,
PRIMARY KEY (users_relationship_id),
INDEX from_user_id(from_user_id),
INDEX to_user_id (to_user_id),
INDEX from_user_id_to_user_id (from_user_id, to_user_id),
INDEX from_user_id_to_user_id_users_relationship_type (from_user_id, to_user_id, users_relationship_type)
);

Backticks are cute. But needed only when the the names would otherwise violate reserved words. Though they are generated by tools for create table dumps, why bother with them unless you really hate your little left pinky. Just my opinion.

Upvotes: 1

M0rtiis
M0rtiis

Reputation: 3774

first u should replace all your single quotes with backticks.

CREATE TABLE `users_relationships` (
`users_relationship_id` INT(10) NOT NULL,
`from_user_id` INT(10) NOT NULL,
`to_user_id` INT(10) unsigned NOT NULL,
`users_relationship_type` VARCHAR(10) NOT NULL,
`users_relationship_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`users_relationship_id`),
INDEX `from_user_id` (`from_user_id`),
INDEX `to_user_id` (`to_user_id`),
INDEX `from_user_id_to_user_id` (`from_user_id`, `to_user_id`),
INDEX `from_user_id_to_user_id_users_relationship_type` (`from_user_id`, `to_user_id`, `users_relationship_type`));

second u cannot have DATETIME DEFAULT CURRENT_TIMESTAMP

or DATETIME DEFAULT '' or TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Upvotes: 1

q.Then
q.Then

Reputation: 2753

If you want to use MySQL quotes, you need to use `, or else MySQL will mess up and think there is some string being created instead of table columns (typically for column names, you don't use ', or else MySQL will think a string is being used for data selection)

CREATE TABLE `users_relationships` (
`users_relationship_id` INT(10) NOT NULL,
`from_user_id` INT(10) NOT NULL,
`to_user_id` INT(10) unsigned NOT NULL,
`users_relationship_type` VARCHAR(10) NOT NULL,
`users_relationship_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`users_relationship_id`),
INDEX `from_user_id` (`from_user_id`),
INDEX `to_user_id` (`to_user_id`),
INDEX `from_user_id_to_user_id` (`from_user_id`, `to_user_id`),
INDEX `from_user_id_to_user_id_users_relationship_type` (`from_user_id`, `to_user_id`, `users_relationship_type`));

Here's the working SQLFiddle here

Upvotes: 1

Related Questions