SQL - CREATE TABLE
Creation of a basic table in an SQL Scheme requires Name of the Table, Name(s) of Attribute(s), it's Data Types and size, Various Constraints, etc.
The basic syntax of CREATE TABLE statement is
CREATE TABLE <Table Name>( Column_Name1 Data_Type (Column_Width)[Constraints], [Column_Name2 Data_Type (Column_Width)[Constraints], --------------------------------------------------, ]);
where table name assigns the name of the table, column name defines the name of the field, data type specifies the data type for the field and column width allocates specified size to the field.
Note:
- Table name should start with an alphabet.
- In table name, blank spaces and single quotes are not allowed.
- Table name is cas sensitive
- Reserve words of that DBMS can not be used as table name.
- Proper data types and size should be specified.
- Unique column name should be specified.
For example:
Create a table 'DEPARTMENT' with attributes department-id, name of the department and location of the department with suitable datatypes and sizes. Choose department-id as primary key.
mysql> create table department (dept_id varchar(3) primary key,
-> dept_name varchar(30) not null,
-> location varchar(30));
Query OK, 0 rows affected (0.44 sec)
mysql>
The command can be written alternatively as,
mysql> create table department (dept_id varchar(3),
-> dept_name varchar(30) not null,
-> location varchar(30),
-> primary key(dept_id));
Query OK, 0 rows affected (0.37 sec)
mysql>
Here,
Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.
Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.