Mysql syntax error for creating table

Question

I want to create a table in mysql:--

create table app_own(app_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
sp_id bigint(20),
title varchar(30),
description varchar(60),
details LONGTEXT(1000),
primary key(app_id ),
FOREIGN KEY (user_id) REFERENCES student(sp_id));

where app_id is the primary key and sp_id is the foreign key reference from student table..

but I am getting error:--

    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 '(1000)0,primary key(app_id ),FOREIGN KEY (sp_id) 
REFERENCE' at line 1

why the error is occuring?

srikanth r · Accepted Answer · 2015-09-10 11:07:27Z

1

Try to Run This one:-

   use db;
CREATE TABLE parent (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ;

CREATE TABLE Persons
(
PID int UNSIGNED  NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City longtext,
PRIMARY KEY (PID),
FOREIGN KEY (PID) 
        REFERENCES parent(id)
 ON DELETE CASCADE
);

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

edited Sep 10, 2015 at 11:07

answered Sep 10, 2015 at 10:56

srikanth r

3324 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Salini Over a year ago

I have tried your code..I got message:--ERROR 1005 (HY000): Can't create table 'mydatabase.persons' (errno: 150)

srikanth r Over a year ago

Have u used Unsigned for the primary key of parent table

srikanth r Over a year ago

Is there any table with the name Parent in the DB where u r trying to create table

Salini Over a year ago

Nopes..Its clean..I have copied and pasted your code

srikanth r Over a year ago

have u replaced db with ur dbname

Simimmo · Accepted Answer · 2015-09-10 09:54:51Z

0

You should user varchar(1000) in place of longtext(1000).

Moreover user_id column doesn't exist in you table.

answered Sep 10, 2015 at 9:54

Simimmo

6681 gold badge6 silver badges15 bronze badges

Comments

Alucard · Accepted Answer · 2015-09-10 09:58:27Z

0

You can´t specify the LONGTEXT in this context. Have a look in the manual.

Change your code to:

create table app_own(app_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
sp_id bigint(20),
title varchar(30),
description varchar(60),
details LONGTEXT,
primary key(app_id),
FOREIGN KEY (user_id) REFERENCES student(sp_id));

answered Sep 10, 2015 at 9:58

Alucard

3171 gold badge4 silver badges15 bronze badges

Comments

Rick · Accepted Answer · 2015-09-10 09:59:24Z

0

Also, you shouldn't be using spaces inbetween the brackets at primary key(app_id).

answered Sep 10, 2015 at 9:59

Rick

1,22913 silver badges26 bronze badges

Collectives™ on Stack Overflow

Mysql syntax error for creating table

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related