Introduction
The database is a collection of data organized as tables, schemas, views, reports, etc. It allows efficient retrieval, insertion, or deletion of the data.
For example, College data is organized about students, faculties, courses, staff, etc., bank data is collected about customers, account names, transactions, etc. This data management helps in easy retrievals, insertion, and deletion of data.
A table is a collection of related organized data. It contains columns and rows.
To create a table in SQL, we need to add the table’s name, define its columns, and define each column’s data type.
Syntax
|
CREATE TABLE table_name(
|
The CREATE TABLE keyword tells the database to create a new table.
Must Read SQL Clauses
MySQL CREATE TABLE
Syntax
|
CREATE TABLE [IF NOT EXISTS] table_name( column_3_definition, . .., |
Let's understand the syntax better.
- After the CREATE TABLE keywords, you specify the table's name you want to create. Within a database, the table name must be unique. The IF NOT EXISTS statement is not necessary. It lets you see if the table you're creating is already in the database. MySQL will ignore the entire command if this is the case, and no new table will be created.
- Second, in the column list part, you provide a list of the table's columns, separated by commas.
- Finally, the ENGINE clause allows you to specify the table's storage engine. Any storage engine, such as InnoDB and MyISAM, can be used. If you don't select a storage engine, MySQL will use InnoDB by default.
The following is the syntax for defining a column:
| columnName dataType(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_constraint; |
The following are the specific details:
- The columnName specifies the column's name. Each column has its data type and length options, such as VARCHAR (255).
- The NOT NULL constraint prevents the column from containing NULL values. A column may have extra constraints, such as CHECK and UNIQUE, in addition to the NOT NULL condition.
- The DEFAULT provides the column's default value.
- The AUTO_INCREMENT specifies that the column's value is automatically incremented by one whenever a new row is added to the database. A maximum of one AUTO_INCREMENT column exists in each table.
- Table constraints such as UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY can be defined after the column list.
A Foreign Key is a field in a table that refers to the PRIMARY KEY in a different table. The child table is the one with the foreign key, while the referenced or parent table has the primary key.
A primary key is set of columns( or a single column) that uniquely identifies the rows in that table. You can create a unique identifier for each entry in your database using the primary key. It's essential as it allows you to use primary keys to link your table to other tables. To set a column or a group of columns as the primary key, use the following syntax:
| PRIMARY KEY (col1,col2,...) |
Example:
In the below given an example, we created a new STUDENTS table with an ID as a primary key, and NOT NULL are the constraints showing that these fields cannot be NULL.
|
CREATE TABLE STUDENTS( ID INT, NOT NULL, NAME VARCHAR (25), NOT NULL, AGE INT, NOT NULL, EMAIL VARCHAR (50), PRIMARY KEY (ID) ); |
You can also use DESC/ DESCRIBE command to check if the table is created or not as follows:
| SQL > DESC STUDENTS; |
DESC followed by table name.
Now, you have a STUDENT table available in your database.
Recommended topics, Schema in DBMS and Tcl Commands in SQL
8+ registered 