MySQL Create Table Statement

The MySQL Create Table

MySQL Create Table Statement
MySQL Create Table Statement

Creating Tables in MySQL

Tables are defined with the CREATE TABLE command. When a table is created, its columns are named, data types and sizes are supplied for each column. Each table must have at least one column.
The syntax of CREATE TABLE command is :

CREATE TABLE <table-name>
          (<column name> <data type> [(<size>)],
          (<column name 2> <data type> [(<size>) ...] ;

To create an employee table whose schema is as follows :
employee(ecode, ename, sex, grade, gross)

the SQL command will be

CREATE TABLE employee
( ecode integer,
  ename char(20),
  sex char(1),
  grade char(2),
  gross decimal ) ;

Inserting Data into Table

The rows(tuples) are added to relations using INSERT command of SQL. In its simplest form, INSERT takes the following syntax :

INSERT INTO <table-name> [<column list>]
VALUES (<value>, <value> ...) ;

For example, to enter a row into employee table, you could use the following statement :

INSERT INTO employee
VALUES (1001, 'Ravi', 'M', 'E4', 4670.00) ;

The INSERT statement adds a new rows to employee giving a value for every column in the row. Note that the data values are in the same order as the column names in the table. Data can be added only to some columns in a row by specifying the columns and their data.
For instance, if you want only ecode, ename and sex columns, you use the command :

INSERT INTO employee (ecode, ename, sex)
VALUES (2014, 'Manju', 'F') ;

The columns that are not listed in the INSERT command will have their default value, if it is defined for them, otherwise, NULL value.
If any other column (that does not have a default value and is defined NOT NULL) is skipped or omitted, an error message is generated and the row is not added.

Inserting NULL values

To insert value NULL in a specific column, you can type NULL without quotes and NULL will be inserted in that column.
Consider the following statement :

INSERT INTO EMPL (Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
Values (8100, 'YASH', 'ANALYST', NULL, '10-MAY-03', 6000, NULL, 20) '

See, for Mgr and Comm columns, NULL values have been inserted.


Inserting Dates

Dates are by default entered in ‘YYYY-MM-DD’ format i.e., first four digits depicting year, followed by a hyphen, followed by 2 digits of month, followed by a hyphen and a two digit day. All this is enclosed in single quotes.


Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.
The new table gets the same column definitions. All columns or specific columns can be selected.
If you create a new table using an existing table, the new table will be filled with existing values from the old table.


Syntax

CREATE TABLE new_table_name AS
      SELECT column1, column2, ...
      FROM existing_table_name
      WHERE ....;

The following SQL creates a new table called “TestTable” (which is a copy of the “Customers” table) :
Example

CREATE TABLE TestTable AS 
SELECT customername, contactname
FROM customers ;

Creating Tables with SQL Constraints

SQL Constraints
Common types of constrains that are applied on table columns include the following :

S.NO.ConstraintsDescription
1.NOT NULLEnsures that a column cannot have NULL value.
2.DEFAULTProvides a default value for a column when none is specified.
3.UNIQUEEnsures that all values in a column are different.
4.CHECKMakes sure that all values in a column satisfy certain criterial.
5.PRIMARY KEYUsed to uniquely identify a row in the table.
6.FOREIGN KEYUsed to ensure referential integrity of the data.
MySQL Create Table with SQL Constraints

(a) SQL NOT NULL Constraint

By default, a column can hold NULL. If you do not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value. For example, in following statement,

CREATE TABLE Customer
( SID integer NOT NULL,
   LAST_Name varchar (30)NOT NULL,
   First_Name varchar(30) ) ;

Columns SID and Last_Name cannot include NULL, while First_Name can include NULL.
An attempt to execute the following SQL statement,

INSERT INTO Customer (Last_Name, First_Name)
VALUES ('Wang', 'Perdro') ;

will result in an error because this will lead to column SID being NULL, which violates the NOT NULL constraint on that column.

(b) SQL DEFAULT Constraint

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.
For example, if we create a table as below :

CREATE TABLE Student
( Student_ID integer Unique,
   Last_Name varchar (30),
   First_Name varchar (30),
   Score DEFAULT 80 ) ;

and execute the following SQL statement,

INSERT INTO Student (Student_ID, Last_Name, First_Name)
VALUES('10', 'Qureshi', 'Zeeshan') ;

After this SQL query, the table will look like the following :

Student_IDLast_NameFirst_NameScore
10QureshiZeeshan80
Table : Student

Even though we didn’t specify a value for the score column in the INSERT INTO statement, it does get assigned the default value of 80 since we had already set 80 as the default value for this column.

(c) SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct. In other words, no two rows can hold the same value for a column with UNIQUE constraint.
For example, in the following CREATE TABLE statement,

CREATE TABLE Customer
( SID integer UNIQUE,
   Last_Name varchar (30),
   First_Name varchar (30) ) ;

Column SID has a unique constraint, and hence cannot include duplicate values. Such constraint does not hold for columns Last_Name and First_Name. So, if the table already contains the following rows :

SIDLast_NameFirst_Name
1WangPedro
2QureshiZeeshan
3RastogiRajiv

Executing the following SQL statement,

INSERT INTO Customer
VALUES ('3', 'Cyrus', 'Grace') ;

will result in an error because the value 3 already exists in the SID column, thus trying to insert another row with that value violates the UNIQUE constraint.


(d) SQL CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy certain conditions. Once defined, the database will only insert a new row or update an existing row if the new value satisfy the CHECK constraint. The CHECK constraint is used to ensure data quality For example, in the following CREATE TABLE statement,

CREATE TABLE Customer
( SID integer CHECK (SID>0),
   Last_Name varchar (30),
   First_Name varchar (30) ) ;

(e) Primary Key Constraint

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself, or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

Defining primary key through Create Table Command

You can define a primary key in CREATE TABLE command through keywords PRIMARY KEY.
Example

CREATE TABLE Customer
( SID integer not null PRIMARY KEY,
  Last_Name varchar(30),
  First_Name varchar(30) ) ;

The latter way is useful if you want to specify a composite primary key (i.e., having a group of fields) e.g.,

CREATE TABLE Customer
( Branch integer not null,
  SID integer not null,
  Last_Name varchar(30),
  First_Name varchar(30) 
   PRIMARY KEY (Branch, SID) ) ;
Defining Primary Key through Alter Table Command


Syntax

ALTER TABLE <table-name>
ADD PRIMARY KEY (<key-field>)
Example
ALTER TABLE Customer
ADD PRIMARY KEY (SID) ;

(f) Foreign Key Constraint

In RDBMS, tables reference one another through common fields and to ensure validity of references, referential integrity is enforced. Referential integrity is a system of rules that a DBMS uses to ensure that relationships between records in related tables are valid, and that users don’t accidentally delete or change related data. Referential integrity is ensured through FOREIGN KEY constraint.
For instance, if we have two tables having structure as given below :

column namecharacteristic
SIDPrimary key
Last_Name
First_Name
Table : CUSTOMER
column namecharacteristic
Order_IDPrimary Key
Order_Date
Customer_SIDForeign Key
Amount
Table : ORDERS

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.
just like primary key, Foreign key can also be created two ways : through CREATE TABLE and ALTER TABLE commands.


Defining Foreign key through Create Table


Syntax

Foreign Key(<column-to-be-designed-as-foreign-key>) references
Master-Table(<primary-key-of-master-table>) ;

Example

CREATE TABLE ORDERS
( order_ID integer,
  order_Date date,
  Customer_SID integer,
  Amount double,
  primary key (orde_ID),
 Foreign Key (Customer_SID) references CUSTOMER(SID) ) ;

The above code will designate Customer_SID field of ORDERS table as foreign key referencing SID field of CUSTOMER table.

Defining Foreign Key through Alter Table

Syntax

ALTER TABLE <table-name>
ADD FOREIGN KEY (<column-to-designed-as-foreign-key>)
references Master-Table (<primary-key-of-master-table>) ;

Following example specifies a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been put in :

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_SID) References CUSTOMER(SID) ;

2. Applying Table Constraints

When a constraint is to be applied on a group of columns of the table, it is called table constraint. The table constraints appear in the end of table definition. For instance, if you want combination of icode and descp of table items to be unique, you may write it as follows :

CREATE TABLE items
( icode char (5) NOT NULL,
 descp char (20) NOT NULL,
 ROL integer,
 QOH integer,
 CHECK (ROL < QOH),                           these are table constraints
 UNIQUE (icode, descp) ) ;

The above statement ensures that the combination of icode and desc in each row must be unique.

A constraint applied on one column (e.g., as you define not null with a column definition) is known as column constraints.

Read More :

MySQL Commands with Examples

MySQL ORDER BY with Examples

MySQL Group BY with Examples

MySQL Create Table

MySQL Create Table Statement video

MySQL Create Table Statement video Credit : The Bad Tutorials

Frequently Asked Questions

Related MySQL Create Table Statement

How do you get the create statement of a table in MySQL?

It is very simple in MY SQL Workbench ( I am using Workbench version 6.3 and My SQL Version 5.1 Community edition): Right click on the table for which you want the create script, select ‘Copy to Clipboard –> Create Statement‘ option. Simply paste in any text editor you want to get the create script.
MySQL Create Table Statement Tutorial

How do I create a SQL table with select statements?

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
MySQL Create Table Statement Tutorial

What is key in MySQL Create Table?

KEY is the synonym for INDEX . You use the KEY when you want to create an index for a column or a set of columns that is not the part of a primary key or unique key. A UNIQUE index ensures that values in a column must be unique. Unlike the PRIMARY index, MySQL allows NULL values in the UNIQUE index.
MySQL Create Table Statement Tutorial

How do I create a .SQL table in MySQL?

MySQL Create Table statement with examples
CREATE TABLE ‘schemaname’.’ tablename'(
column_1 datatype (length) NOT NULL | DEFAULT | UNIQUE,
…,
Primary key,
Foreign key.
) ENGINE=storage_engine;
MySQL Create Table Statement Tutorial

How do I display a table in SQL?

SQL command to list all tables in Oracle
Show all tables owned by the current user: SELECT table_name FROM user_tables; Code language: SQL (Structured Query Language) (sql)
Show all tables in the current database: SELECT table_name FROM dba_tables; …
Show all tables that are accessible by the current user:
MySQL Create Table Statement Tutorial

How do you create a database table?

Create a new table in an existing database
Click File > Open, and click the database if it is listed under Recent. If not, select one of the browse options to locate the database.
In the Open dialog box, select the database that you want to open, and then click Open.
On the Create tab, in the Tables group, click Table.
MySQL Create Table Statement Tutorial

Does select into create a table?

The SELECT INTO statement creates a new table and inserts rows from the query into it. If you want to copy the partial data from the source table, you use the WHERE clause to specify which rows to copy.
MySQL Create Table Statement Tutorial

How do you create a table?

Open a blank Word document.
In the top ribbon, press Insert.
Click on the Table button.
Either use the diagram to select the number of columns and rows you need, or click Insert Table and a dialog box will appear where you can specify the number of columns and rows.
The blank table will now appear on the page.
MySQL Create Table Statement Tutorial

How do you insert data into a table?

SQL INSERT – Inserting One or More Rows Into a Table
First, the table, which you want to insert a new row, in the INSERT INTO clause.
Second, a comma-separated list of columns in the table surrounded by parentheses.
Third, a comma-separated list of values surrounded by parentheses in the VALUES clause.

MySQL Create Table Statement Tutorial

Can MySQL table have 2 primary keys?

You can only have one primary key, but you can have multiple columns in your primary key. You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values.
MySQL Create Table Statement Tutorial

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *