SQL syntax error

1

Hey,
I have a sql syntax error, but just cant find the mistake…

CREATE DATABASE IF NOT EXISTS Postsandbuy;
USE Postsandbuy;

CREATE TABLE `posts` (
  `post_id` int(10) NOT NULL,
  `post_title` varchar(255) NOT NULL,
  `post_content` varchar(255) NOT NULL,
  `post_time` datetime NOT NULL,
  `isforsale` tinyint(1) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `sold_file` varchar(255) DEFAULT NULL,
  `state` enum('Open','Closed') DEFAULT NULL,
  `buyer_id` int(11) DEFAULT NULL,
  `poster_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `users` (
  `usr_id` int(10) NOT NULL,
  `usr_username` varchar(255) NOT NULL,
  `usr_password` varchar(255) NOT NULL,
  `usr_email` varchar(255) NOT NULL,
  `usr_country` varchar(255) NOT NULL,
  `usr_age` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `abos`(
`usr_id` int(10) NOT NULL ,
`abo_id` int(10) NOT NULL,
FOREIGN KEY (usr_id) REFERENCES users(usr_id),
FOREIGN KEY (abo_id) REFERENCES users(usr_id)
)

ALTER TABLE `posts`
  ADD PRIMARY KEY (`post_id`),
  ADD KEY `buyer_id` (`buyer_id`),
  ADD KEY `poster_id` (`poster_id`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`usr_id`);

ALTER TABLE `posts`
  MODIFY `post_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

ALTER TABLE `users`
  MODIFY `usr_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `posts`
  ADD CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`buyer_id`) REFERENCES `users` (`usr_id`),
  ADD CONSTRAINT `posts_ibfk_2` FOREIGN KEY (`poster_id`) REFERENCES `users` (`usr_id`);

insert into users (usr_id, usr_username) VALUES (0, "Anonymous");

Any help would be amazing…

2

I cleaned up your code.
You need to use triple backticks to post code to the forum.
See this post for details.

3

Try changing this to just AUTO_INCREMENT=16 and the same with the one below it.

Also, if you set a field to auto increment, you can’t set its value as far as I know
insert into users (usr_id, usr_username) VALUES (0, "Anonymous");
should be
insert into users(usr_username)Values ('Anonymous')

And the last thing I noticed is you’re using double quotes ( " ) around your string values. SQL uses single quotes( ’ ).

4

Thank you…

5

I still won´t work, the error message is:

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 
'ALTER TABLE `posts`
  ADD PRIMARY KEY (`post_id`),
  ADD KEY `buyer_id` (`buye' at line 8
6

In the code you’re pasting, it looks like you’re using back-ticks instead of single quotes. Try replacing all of your “single quotes” with the one by the “Enter” key (if you have a US formatted keyboard).

7

It still won’t work, but it has a new error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''posts' (
  'post_id' int(10) NOT NULL,
  'post_title' varchar(255) NOT NULL,
' at line 1
8

I looked at your code a little more closely. You need to remove all of the quotes around your table and column names. Quotes represent literal strings, but you need to be creating database objects so they can’t have quotes around them. Also, You have a number next to your int/smallint type columns. Those need to be removed as well. You can’t designate a size for integer type fields.I’m haven’t seen the enum declaration before so I don’t know if that is valid syntax or not. I also don’t know what the ENGINE= part does. I copied your code in my SSMS and those were the issues I found.

1 Like
10

CREATE TABLE posts (
post_title varchar(255) NOT NULL,
post_content varchar(255) NOT NULL,
post_time datetime NOT NULL,
isforsale tinyint(1) DEFAULT NULL,
price int(11) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
filename varchar(255) DEFAULT NULL,
sold_file varchar(255) DEFAULT NULL,
state enum(‘Open’,‘Closed’) DEFAULT NULL,
post_id int(10) NOT NULL,
buyer_id int(11) DEFAULT NULL,
poster_id int(11) DEFAULT NULL,
CONSTRAINT posts_pk PRIMARY KEY (post_id, buyer_id, poster_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Primary key columns cannot be null and must be unique.
Another way to define composite primary key is :slight_smile:

ALTER TABLE posts
ADD CONSTRAINT posts_pk
PRIMARY KEY (post_id, buyer_id, poster_id);

11

Please find the error in below query.

CREATE TABLE BONUS(
WORKER REF ID INT,
BONUS AMOUNT INT(10),
BONUS DATE DATETIME,
FOREIGN KEY (WORKER REF ID)
REFERENCES WORKER(WORKER ID)
ON DELETE CASCADE
);

12

CREATE TABLE booking_table ( book_id int(11) NOT NULL, banq_id int(11) DEFAULT NULL, event_date datetime DEFAULT NULL, session int(11) DEFAULT NULL, menu int(11) DEFAULT NULL, package int(11) DEFAULT NULL, no_of_person int(11) DEFAULT NULL, event_type int(11) DEFAULT NULL, price varchar(255) DEFAULT NULL COMMENT ‘price per plate’, cost varchar(255) DEFAULT NULL, cgst decimal(10,0) DEFAULT NULL, sgst decimal(10,0) DEFAULT NULL, gtotal decimal(10,0) DEFAULT NULL, book_amount varchar(255) DEFAULT NULL, user int(11) DEFAULT NULL, items text, additional_counter text, additional_counter_item text, additional_price varchar(255) DEFAULT NULL, hi_tea_menu int(11) DEFAULT NULL, hi_tea_package int(11) DEFAULT NULL, hi_tea_person int(11) DEFAULT NULL, hi_tea_item text, hi_tea_price varchar(255) DEFAULT NULL, first_name varchar(255) DEFAULT NULL, last_name varchar(255) DEFAULT NULL, pan_no varchar(255) DEFAULT NULL, gender varchar(255) DEFAULT NULL, email varchar(500) DEFAULT NULL, phone varchar(255) DEFAULT NULL, room_detail text, remark text, receipt text, from_date datetime DEFAULT NULL YYYY-MM-DD HH:MI:SS, to_date datetime DEFAULT NULL YYYY-MM-DD HH:MI:SS, is_tentative int(11) NOT NULL DEFAULT YYYY-MM-DD HH:MI:SS, status int(11) DEFAULT ‘0’ COMMENT ‘0-default,1-confirm,2-cancel’, date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

(Can any one solve this error)

13

The better answer. It works for me. Thanks