CODEWITHSUNDEEP
mysqldatabasecreate-table
Salini
Salini

Reputation: 347

Mysql syntax error for creating table

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?

Upvotes: 0

Views: 888

Answers (4)

srikanth r
srikanth r

Reputation: 332

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.

Upvotes: 1

Rick
Rick

Reputation: 1209

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

Upvotes: 0

Alucard
Alucard

Reputation: 317

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

Upvotes: 0

Simimmo
Simimmo

Reputation: 668

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

Moreover user_id column doesn't exist in you table.

Upvotes: 0

Related Questions