MySQL CREATE TABLE

In this tutorial, we are going to see how to create a MySQL table. CREATE TABLE statement is used to create a new table in a database. The CREATE TABLE statement requires three things:

• Table name
• Field names
• Definitions for each field

 

Syntax:

CREATE TABLE tableName (
	column1 type,
	column2 type,
	column3 type,
	...
);

 

Example: Creating a MySQL table

The following statement creates a new table called “Persons”:

CREATE TABLE Persons (
    PersonID int AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Age int,
    Address VARCHAR(100)
);

“Persons” table contains the following columns:

• PersonID is a column that automatically increments. If you use the INSERT statement to insert a new row into the table without specifying a value for the PersonID column, MySQL will automatically generate a sequential integer for the PersonID column starting at 1.
• Name column is a variable string column with a maximum length of 20. This means that you cannot insert strings longer than 20 in this column. The NOT NULL constraint indicates that the column does not accept a NULL value. In other words, you must provide a non-NULL value when you insert or update this column.
• The same goes for the Address column, except that it accepts a NULL value
• The Age column is an int column that accepts a NULL value.

The column PersonID is a primary key of “Persons” table. This means that the values in the column PersonID uniquely identify the rows in the table.

Once you have executed the CREATE TABLE statement to create the “Persons” table, you can display its structure using the following command:

> DESCRIBE Persons;

+-----------+-----------------+--------+------+----------+----------------+
|  Field    |       Type      |  Null  |  Key |  Default |     Extra      |
+-----------+-----------------+--------+------+----------+----------------+
|  PersonID | int<11>         |   No   |  PRI | NULL     | auto_increment |
|  Age      | int<10>         |   Yes  |      | NULL     |                |
|  Name     | varchar<20>     |   Yes  |      | NULL     |                |
|  Address  | varchar<255>    |   Yes  |      | New York |                |
+-----------+-----------------+--------+------+----------+----------------+

 

Example of a foreign key

Suppose each person has placed orders. To store the orders, you can create a new table named “Orders”:

CREATE TABLE Orders (
    OrderID int AUTO_INCREMENT PRIMARY KEY,
    NumOrder int NOT NULL,
    PersonID int,
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

The “PersonID” column is a foreign key that refers to the “PersonID” column of the “Persons” table. We used the Foreign Key constraint to establish this relationship:

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

 

Database schema

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More