MySQL - More About Create Table

When creating a table in MySQL, there are several options that can be used to define the properties of the columns. Here are the most commonly used options with examples:

NOT NULL: This option specifies that the column cannot have a NULL value.

Example:

CREATE TABLE students (

  id INT NOT NULL,

  name VARCHAR(50) NOT NULL,

  age INT

);

In this example, both the "id" and "name" columns are defined as NOT NULL, which means they must have a value for each row. However, the "age" column is not defined as NOT NULL, which means it can have NULL values.

NULL: This option specifies that the column can have NULL values.

Example:

CREATE TABLE employees (

  id INT,

  name VARCHAR(50),

  address VARCHAR(100) NULL,

  phone VARCHAR(20) NULL

);

In this example, the "id" and "name" columns do not have the NULL option specified, which means they cannot have NULL values. However, the "address" and "phone" columns are defined as NULL, which means they can have NULL values.

DEFAULT: This option specifies a default value for the column if no value is specified during insertion.

Example:

CREATE TABLE orders (

  id INT,

  customer_id INT,

  order_date DATE DEFAULT CURRENT_DATE,

  total DECIMAL(10,2) DEFAULT 0.00

);

In this example, the "order_date" column is defined with a DEFAULT value of CURRENT_DATE, which means that if no value is specified during insertion, the current date will be used. Similarly, the "total" column is defined with a DEFAULT value of 0.00, which means that if no value is specified during insertion, the total will be set to 0.00.

UNIQUE: This option specifies that the column must contain unique values for each row in the table. However, unlike PRIMARY KEY, the column can contain NULL values.

Example:

CREATE TABLE employees (

  id INT,

  name VARCHAR(50),

  email VARCHAR(100) UNIQUE

);

In this example, the "email" column is defined as UNIQUE, which means that it must contain unique values for each row. However, the column can contain NULL values.