CREATE TABLE

mysql

What is the syntax for creating a table?

CREATE TABLE tableName(
    colName1 colType options, 
    colName2 colType options, 
    ... 
    [,PRIMARY KEY(colName1,colName2)]
    [,INDEX indexName(colName1, colName2)]
)

See below for more concrete examples.

How can we specify storage engine at table creation time?

CREATE TABLE t (i INT) ENGINE = InnoDB;

How can we create a table with an auto-incremented primary key?

create table questions (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    question VARCHAR(250) NOT NULL,
    hint TEXT,
    filePath VARCHAR(30) NOT NULL,
    displayOrder DECIMAL(3 , 2 )
);

How can we define indexes at table creation time?

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    InaugurationDate Date NOT NULL,
    INDEX (Inauguration)
);

The INDEX keyword is used to create an index that the values that it stores does not need to be unique. To create a unique index, the KEY or UNIQUE keywords may be used instead of INDEX.

How can we create a composite index (an index that contains multiple columns)?

A composite index is an index that contains multiple columns. To create a composite index, list all the column names within parentheses, separated by commas:

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    InaugurationDate DATE NOT NULL,
    INDEX (LastName, FirstName)
);

How can we create multiple indexes using CREATE TABLE:

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    InaugurationDate DATE NOT NULL,
    INDEX (LastName, FirstName),
    INDEX (InaugurationDate)
);

Yes. A table can have multiple indexes. Here is a table that includes two index definitions. The first creates an index on the id column and requires each id to be unique. The second index definition creates a two-column index on the last_name, and first_name columns:

CREATE TABLE t
(
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    UNIQUE (id),
    INDEX (last_name, first_name)
);

How can we create a PRIMARY KEY index that span multiple columns?

CREATE TABLE people
(
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (last_name, first_name)
);

How can we create an index with a name using CREATE TABLE?

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    INDEX NameIndex (LastName, FirstName),
    UNIQUE IDIndex (ID)
);

How can we specify and indexing algorithm at table creation time?

CREATE TABLE table_name
(
    id INT
    INDEX USING BTREE (id)
) ENGINE = MEMORY;

How can we create a table if it does not currently exists?

CREATE TABLE [IF NOT EXISTS] customer(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name char(50) NOT NULL,
    address char(50),
    yesno ENUM('yes','no'),
    truefalse ENUM('true','false'),
    symptom SET('sneezing', 'runny nose', 'stuffy head', 'red eyes'),
    ts1 TIMESTAMP,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    c CHAR(10) CHARACTER SET utf8,
    d DATE DEFAULT '1999-12-31'
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

MySQL does not perform any check on the table structure when you add the IF NOT EXISTS clause. If a table with the given name exists but has a structure different from the one you've defined in the CREATE TABLE statement, MySQL will issue no warning.

How many AUTO_INCREMENT column is allowed per table and what is the other restriction?

Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (for example, by making it a primary key).

Does MySQL allows the use of function as default value?

No. Default value is specified at table creation time. MySQL does not allow the use of function as default value.

How can we specify default value?

Many database developers use DEFAULT values instead of NULL, especially in columns that will be used in calculation or data groupings.

CREATE TABLE seminar
(
    att_id INT UNSIGNED NOT NULL,
    sem_title ENUM('Database Design', 'Query Optimization', 'SQL Standards', 'Using Replication'),
    INDEX (att_id)
)

How can we create multisequence?

CREATE TABLE multisequence
(
    name CHAR(10) NOT NULL,
    name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (name, name_id)
);

Inserting name values into the multisequence table generates separate sequences for each distinct name.

How can we create a table using a SELECT statement?

CREATE TABLE … SELECT can create a table that is empty or non-empty, depending on what is returned by the SELECT part.

CREATE TABLE CityCopy1 SELECT * FROM City;
CREATE TABLE CityCopy2 SELECT * FROM City WHERE Population > 2000000;
CREATE TABLE CityCopy3 SELECT * FROM City WHERE 0;
CREATE TABLE Capital
 SELECT Country.Name AS Country, City.Name AS Capital
   FROM Country, City WHERE Country.Capital = City.ID

How can we create a table base on another table?

CREATE TABLE … LIKE creates an empty table based on the definition of another table. The result is a new table with a definition that includes all column attributes and indexes of the original table.

CREATE TABLE copy2 LIKE t;

The result of CREATE TABLE … LIKE differs from the result of using CREATE TABLE … SELECT to create an empty table. Either of the following statements will create an empty copy of table t:

CREATE TABLE copy1 SELECT * FROM t WHERE 0;
CREATE TABLE copy2 LIKE t;

However, the resulting copies differ in the amount of information retained from the original table structure. The CREATE TABLE … SELECT statement copies the column name and data type from the original table, but does not retain the PRIMARY KEY index information or AUTO_INCREMENT column attribute information. The new table also use the default storage engine, rather than the storage engine utilized by table t.

Some table attributes are not copied, even when using CREATE TABLE … LIKE. The most notable examples are:

  • If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table. The data and index files for the new table will reside in the database directory for the chosen database.
  • Foreign key definition in the original table are not copied to the new table. If you wish to retain the foreign key definitions, they must be re-specified with ALTER TABLE after creating the copy.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License