MySQL - PhpMyadmin - Create Multiple Tables

Question

I can´t understand why this query is not working to create this table structure in PHPMyAdmin.

I always receive this error message:

1064 - 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 'MAX), FOREIGN KEY (form_id) REFERENCES form (Id) )' at line 4

CREATE TABLE form(
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(500),
    Is_Active BIT,
    Is_Trash BIT,
    Date_Created DATETIME
);
CREATE TABLE form_data(
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    form_id INT,
    formdata VARCHAR(MAX),
    FOREIGN KEY (form_id) REFERENCES form (Id)
);

Mureinik · Accepted Answer · 2016-03-26 15:34:43Z

3

varchar(max) is MS SQL Server's syntax. MySQL doesn't have an equivalent, so you'll just have to use a very long size. Additionally, once you'll solve this problem, you'll encounter a problem where form_data.form_id is not of the the same type as form.id (one is unsigned and the other isn't), so it cannot reference it. In short:

CREATE TABLE form_data(
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    form_id INT UNSIGNED, -- Second issue
    formdata VARCHAR(4000), -- First issue 
    FOREIGN KEY (form_id) REFERENCES form (Id)
);

answered Mar 26, 2016 at 15:34

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

alexander.polomodov · Accepted Answer · 2016-03-26 15:34:44Z

2

There are 2 problems in table form_data:

type of form_id, it should be INT UNSIGNED to match type of Id type of table form
Using formdata VARCHAR(MAX), you should change MAX to real integer, 255 for example

Try this code:

CREATE TABLE form(
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(500),
    Is_Active BIT,
    Is_Trash BIT,
    Date_Created DATETIME
);
CREATE TABLE form_data(
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    form_id INT UNSIGNED,
    formdata VARCHAR(255),
    FOREIGN KEY (form_id) REFERENCES form (Id)
);

answered Mar 26, 2016 at 15:34

alexander.polomodov

5,54414 gold badges44 silver badges48 bronze badges

Comments

Vincent Decaux · Accepted Answer · 2016-03-26 15:34:49Z

1

Your error come from :

formdata VARCHAR(MAX),

Change MAX to a value.

answered Mar 26, 2016 at 15:34

Vincent Decaux

10.9k7 gold badges67 silver badges103 bronze badges

Collectives™ on Stack Overflow

MySQL - PhpMyadmin - Create Multiple Tables

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related