CODEWITHSUNDEEP
mysql
user6732041
user6732041

Reputation:

Does the order of table parameters matter in MySQL queries

I find that when I'm trying to make a mysqli query, it will fail unless I order the parameters in a certain way.

//Example that causes error (focus on order of AUTO_INCREMENT and UNSIGNED)

<?php
$con = new mysqli("localhost", "user", "pass", "db");

$query = "CREATE TABLE colors (
id INT(2) AUTO_INCREMENT UNSIGNED PRIMARY KEY,
color1 varchar(20) NOT NULL,
color2 varchar(20) NOT NULL
)";

$con->query($query);
?>

However, when AUTO_INCREMENT and UNSIGNED switch places, the query is successful. The same thing seems to happen to me when the PRIMARY KEY parameter is not the last parameter listed. Does the order matter?

Upvotes: 1

Views: 146

Answers (1)

Clay
Clay

Reputation: 4760

Yes, please take a look at the MySQL documentation for the CREATE TABLE syntax: http://dev.mysql.com/doc/refman/5.7/en/create-table.html

Specifically this section for column_definition:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
      [NOT NULL | NULL] [[PRIMARY] KEY]

The format and order matters. The documentation can help you to determine the correct order of parameters.

Upvotes: 1

Related Questions