w3resource

MySQL CONSTRAINT

CONSTRAINT

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database.

MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

MySQL CONSTRAINTS can be classified into two types - column level and table level.

The column level constraints can apply only to one column where as table level constraints are applied to the entire table.

MySQL CONSTRAINT is declared at the time of creating a table.

MySQL CONSTRAINTs are:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT
CONSTRAINT DESCRIPTION
NOT NULL In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.
FOREIGN KEY A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT In a MySQL table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

Syntax: 

CREATE  TABLE [table name]                                   
([column name] [data type]([size]) [column constraint]….
[table constraint] ([[column name]……])……);

Explanation:

The above MySQL code shows how to create a table with some constraints. Constraints are applied to columns as well as tables.

You can replace table name, column name, data type and size with your own.

Contents:

MySQL CREATE TABLE with NULL CONSTRAINT

Using the default value as NOT NULL, while creating a MySQL table, it can be enforced that a column in a table is not allowed to store NULL values.

If you want to create a table 'newauthor' where no columns are allowed to store NULL VALUES the following statement can be used.

Example:

If you want to create a table 'newauthor' where no columns are allowed to store NULL VALUES the following statement can be used. 


-- Create a new table named 'newauthor' if it does not already exist
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named 'aut_id' to store author IDs as varchar(8)
    aut_id VARCHAR(8) NOT NULL,
    -- Define a column named 'aut_name' to store author names as varchar(50)
    aut_name VARCHAR(50) NOT NULL,
    -- Define a column named 'country' to store countries as varchar(25)
    country VARCHAR(25) NOT NULL,
    -- Define a column named 'home_city' to store home cities as varchar(25)
    home_city VARCHAR(25) NOT NULL
);

Explanation:

  • CREATE TABLE IF NOT EXISTS newauthor:

  • This line creates a new table named 'newauthor' if it doesn't already exist.

  • IF NOT EXISTS ensures that the table is only created if it does not already exist, preventing errors if the table has already been created.

  • (aut_id varchar(8) NOT NULL, aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL):

  • This block defines the structure of the table.

  • aut_id is defined as a column to store author IDs as varchar(8) and is set to NOT NULL, meaning it must have a value for each row.

  • aut_name is defined as a column to store author names as varchar(50) and is set to NOT NULL.

  • country is defined as a column to store countries as varchar(25) and is set to NOT NULL.

  • home_city is defined as a column to store home cities as varchar(25) and is set to NOT NULL.

The following picture shows that the columns will not accept the NULL values.

mysql not null constraint

MySQL CREATE TABLE to check values with CHECK CONSTRAINT

Adding a CHECK CONSTRAINT on a column of a table, you can limit the range of values allowed to be stored in that column.

Example:

If you want to create a table 'newbook_mast' with a PRIMARY KEY on 'book _id' column, a unique constraint on 'isbn_no' column and a set the no_page in such, that it would hold values more than zero only, the following statement can be used.


-- Create a new table if it does not already exist, named "newbook_mast"
CREATE TABLE IF NOT EXISTS newbook_mast 
(
    -- Define a column named "book_id" with data type VARCHAR(15) that cannot be NULL and must be unique
    book_id varchar(15) NOT NULL UNIQUE,
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "isbn_no" with data type VARCHAR(15) that cannot be NULL and must be unique
    isbn_no varchar(15) NOT NULL UNIQUE,
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "aut_id" with data type VARCHAR(8)
    aut_id varchar(8),
    -- Define a column named "pub_id" with data type VARCHAR(8)
    pub_id varchar(8),
    -- Define a column named "dt_of_pub" with data type DATE
    dt_of_pub date,
    -- Define a column named "pub_lang" with data type VARCHAR(15)
    pub_lang varchar(15),
    -- Define a column named "no_page" with data type DECIMAL(5,0) and a CHECK constraint that ensures it's greater than 0
    no_page decimal(5,0) CHECK(no_page>0),
    -- Define a column named "book_price" with data type DECIMAL(8,2)
    book_price decimal(8,2),
    -- Define the primary key constraint on the "book_id" column
    PRIMARY KEY (book_id)
);

Explanation:

  • This SQL script creates a new table called "newbook_mast" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column in the table.

  • The book_id column is defined with a data type of VARCHAR(15), meaning it can hold strings up to 15 characters in length. It is also marked as NOT NULL and UNIQUE, meaning each value in this column must be unique.

  • Similarly, the isbn_no column is defined with similar properties: NOT NULL and UNIQUE.

  • Other columns like book_name, cate_id, aut_id, pub_id, dt_of_pub, pub_lang, no_page, and book_price are defined with their respective data types.

  • The no_page column has a CHECK constraint that ensures the value is greater than 0.

  • Finally, the PRIMARY KEY constraint is applied to the book_id column, which uniquely identifies each row in the table.

MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator.

Example:

If you want to create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are 'USA','UK' and 'India', the following statement can be used.


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL
    aut_id varchar(8) NOT NULL,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL and has a CHECK constraint to ensure the value is either 'USA', 'UK', or 'India'
    country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')),
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL,
    -- Define the primary key constraint on the combination of "aut_id" and "home_city" columns
    PRIMARY KEY (aut_id, home_city)
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column in the table.

  • The aut_id column is defined with a data type of VARCHAR(8), meaning it can hold strings up to 8 characters in length. It is also marked as NOT NULL.

  • Similarly, the aut_name column is defined with a data type of VARCHAR(50) and marked as NOT NULL.

  • The country column is defined with a data type of VARCHAR(25), marked as NOT NULL, and has a CHECK constraint that ensures the value must be one of 'USA', 'UK', or 'India'.

  • The home_city column is defined with a data type of VARCHAR(25) and marked as NOT NULL.

  • The PRIMARY KEY constraint is applied to the combination of aut_id and home_city columns, ensuring each combination is unique and serving as the primary identifier for each row in the table.

MySQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with LIKE operator.

Example:

The MySQL statement stated below will create a table 'newbook_mast' with a PRIMARY KEY on 'book_id' column and a CHECK constraint to limit value to be stored for the column dt_of_pub along with LIKE operator and another CHECK constraint to column no_page (without using LIKE operator).

CHECK (dt_of_pub LIKE '--/--/----') checks whether the format of the date to be stored is the column dt_of_pub is like '--/--/----'.

Here is the statement. 


-- Create a new table if it does not already exist, named "newbook_mast"
CREATE TABLE IF NOT EXISTS newbook_mast
(
    -- Define a column named "book_id" with data type VARCHAR(15) that cannot be NULL and must be unique
    book_id varchar(15) NOT NULL UNIQUE,
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "isbn_no" with data type VARCHAR(15) that cannot be NULL and must be unique
    isbn_no varchar(15) NOT NULL UNIQUE,
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "aut_id" with data type VARCHAR(8)
    aut_id varchar(8),
    -- Define a column named "pub_id" with data type VARCHAR(8)
    pub_id varchar(8),
    -- Define a column named "dt_of_pub" with data type DATE and a CHECK constraint to ensure the date format is 'MM/DD/YYYY'
    dt_of_pub date CHECK (dt_of_pub LIKE '--/--/----'),
    -- Define a column named "pub_lang" with data type VARCHAR(15)
    pub_lang varchar(15),
    -- Define a column named "no_page" with data type DECIMAL(5,0) and a CHECK constraint that ensures it's greater than 0
    no_page decimal(5,0) CHECK(no_page>0),
    -- Define a column named "book_price" with data type DECIMAL(8,2)
    book_price decimal(8,2),
    -- Define the primary key constraint on the "book_id" column
    PRIMARY KEY (book_id)
);

Explanation:

  • This SQL script creates a new table called "newbook_mast" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column in the table.

  • The book_id column is defined with a data type of VARCHAR(15), meaning it can hold strings up to 15 characters in length. It is also marked as NOT NULL and UNIQUE, ensuring each value in this column is unique.

  • Similarly, the isbn_no column is defined with similar properties: NOT NULL and UNIQUE.

  • Other columns like book_name, cate_id, aut_id, pub_id, pub_lang, no_page, and book_price are defined with their respective data types.

  • The dt_of_pub column is defined with a data type of DATE and a CHECK constraint to ensure the date format is 'MM/DD/YYYY'.

  • The no_page column has a CHECK constraint that ensures the value is greater than 0.

  • Finally, the PRIMARY KEY constraint is applied to the book_id column, which uniquely identifies each row in the table.

MySQL CREATE TABLE with AND and OR operator and CHECK CONSTRAINT

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with AND and OR operator.

Example

The MySQL statement stated below will create a table 'newpublisher' with a PRIMARY KEY on 'pub_id' column and CHECK constraint along with AND and OR operator for country and pub_city columns.

CHECK ((country='India' AND pub_city='Mumbai') OR (country='India' AND pub_city='New Delhi')) checks whether (i)country is INDIA and pub_city is Mumbai OR (ii) country is INDIA and pub_city is New Delhi.

MySQL Manual says "The CHECK clause is parsed but ignored by all storage engines."

Here is the statement. 


-- Create a new table if it does not already exist, named "newpublisher"
CREATE TABLE IF NOT EXISTS newpublisher
(
    -- Define a column named "pub_id" with data type VARCHAR(8)
    pub_id varchar(8),
    -- Define a column named "pub_name" with data type VARCHAR(50)
    pub_name varchar(50),
    -- Define a column named "pub_city" with data type VARCHAR(25)
    pub_city varchar(25),
    -- Define a column named "country" with data type VARCHAR(25)
    country varchar(25),
    -- Define a column named "country_office" with data type VARCHAR(25)
    country_office varchar(25),
    -- Define a column named "no_of_branch" with data type INT(3)
    no_of_branch int(3),
    -- Define a column named "estd" with data type DATE
    estd date,
    -- Define a CHECK constraint to ensure specific conditions are met regarding country and city combination
    CHECK (
        (country='India' AND pub_city='Mumbai')            
        OR (country='India' AND pub_city='New Delhi')
    ),
    -- Define the primary key constraint on the "pub_id" column
    PRIMARY KEY (pub_id)
);

Explanation:

  • This SQL script creates a new table called "newpublisher" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column in the table.

  • The pub_id, pub_name, pub_city, country, country_office, no_of_branch, and estd columns are defined with their respective data types.

  • The CHECK constraint is used to ensure specific conditions are met regarding the combination of country and pub_city columns. In this case, it checks if the publisher is located in either Mumbai or New Delhi and if the country is India.

  • The PRIMARY KEY constraint is applied to the pub_id column, ensuring each value is unique and serving as the primary identifier for each row in the table.

MySQL UNIQUE CONSTRAINT

The UNIQUE constraint creates an index such that, all values in the index column must be unique. An error occurs when any body tries to add a new row with a key value that already exists in that row.

Example:

The MySQL statement stated below will create a table 'newauthor' with a column 'aut_id' which will store unique values only since UNIQUE (aut_id) is used. 


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL
    aut_id varchar(8) NOT NULL,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL
    country varchar(25) NOT NULL,
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL,
    -- Define a UNIQUE constraint on the "aut_id" column
    UNIQUE (aut_id)
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The aut_id, aut_name, country, and home_city columns are defined with their respective data types and marked as NOT NULL, meaning values must be provided for these columns in each row.

  • The aut_id column is further specified with a UNIQUE constraint, ensuring that each value in this column is unique.

  • This constraint prevents duplicate entries for the same author ID, ensuring data integrity in the table.

The picture below shows the structure of the table.

mysql unique constraint sample1

Example of MySQL UNIQUE CONSTRAINT check unique value

The MySQL statement stated below will create a table 'newauthor' with a column 'aut_id' which is meant to store unique values only. Notice that UNIQUE is used within the column definition


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL and must be unique
    aut_id varchar(8) NOT NULL UNIQUE,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL
    country varchar(25) NOT NULL,
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column in the table.

  • The aut_id, aut_name, country, and home_city columns are defined with their respective data types and marked as NOT NULL, meaning values must be provided for these columns in each row.

  • The aut_id column is further specified with a UNIQUE constraint, ensuring that each value in this column is unique.

  • This constraint prevents duplicate entries for the same author ID, ensuring data integrity in the table.

The following picture shows the Structure of the Table

mysql unique constraint sample2

MySQL CREATE TABLE with DEFAULT CONSTRAINT

While creating a table, MySQL allows you assign DEFAULT CONSTRAINTS to columns. DEFAULT is used to set a default value for a column and is applied using DEFAULT default_value; where default_value is the default value set to the column.

Example:

The MySQL statement stated below will create a table 'newpublisher' with a PRIMARY KEY on 'pub_id' column, a CHECK constraint with logical operators for country and pub-city columns and a default value for pub_id, pub_name, pub_city and country columns.

The MySQL statement also sets the default value white space for pub_id, pub_name, pub_city columns and 'India' as a default value for a country column.

Here is the statement below. 


-- Create a new table if it does not already exist, named "newpublisher"
CREATE TABLE IF NOT EXISTS newpublisher
(
    -- Define a column named "pub_id" with data type VARCHAR(8) that cannot be NULL and must be unique. Default value is an empty string.
    pub_id varchar(8) NOT NULL UNIQUE DEFAULT '',
    -- Define a column named "pub_name" with data type VARCHAR(50) that cannot be NULL. Default value is an empty string.
    pub_name varchar(50) NOT NULL DEFAULT '',
    -- Define a column named "pub_city" with data type VARCHAR(25) that cannot be NULL. Default value is an empty string.
    pub_city varchar(25) NOT NULL DEFAULT '',
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL. Default value is 'India'.
    country varchar(25) NOT NULL DEFAULT 'India',
    -- Define a column named "country_office" with data type VARCHAR(25).
    country_office varchar(25),
    -- Define a column named "no_of_branch" with data type INT(3).
    no_of_branch int(3),
    -- Define a column named "estd" with data type DATE.
    estd date,
    -- Define a CHECK constraint to ensure specific conditions are met regarding country and city combination
    CHECK (
        (country='India' AND pub_city='Mumbai')
        OR (country='India' AND pub_city='New Delhi')
    ),
    -- Define the primary key constraint on the "pub_id" column
    PRIMARY KEY (pub_id)
);

Explanation:

  • This SQL script creates a new table called "newpublisher" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The pub_id, pub_name, pub_city, and country columns are defined with their respective data types and marked as NOT NULL. Default values are provided for pub_id, pub_name, and pub_city.

  • The country column has a default value of 'India', which will be used if no value is explicitly provided during insertion.

  • The pub_id column is further specified with a UNIQUE constraint, ensuring that each value in this column is unique.

  • The CHECK constraint ensures that the combination of country and pub_city follows specific conditions.

  • The PRIMARY KEY constraint is applied to the pub_id column, ensuring each value is unique and serving as the primary identifier for each row in the table.

MySQL CREATE TABLE with AUTO INCREMENT

MySQL allows you to set AUTO_INCREMENT to a column. Doing so will increase the value of that column by 1 automatically, each time a new record is added.

Example:

The MySQL statement stated below will create a table 'newauthor' with a PRIMARY KEY on 'id' column and the 'id' column is an auto incremented field.


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "id" with data type INT that cannot be NULL and is automatically incremented for each new row
    id int NOT NULL AUTO_INCREMENT,
    -- Define a column named "aut_id" with data type VARCHAR(8)
    aut_id varchar(8),
    -- Define a column named "aut_name" with data type VARCHAR(50)
    aut_name varchar(50),
    -- Define a column named "country" with data type VARCHAR(25)
    country varchar(25),
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL,
    -- Define the primary key constraint on the "id" column
    PRIMARY KEY (id)
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The id column is defined as an integer (INT) and serves as the primary key for the table. It is marked as NOT NULL and is set to automatically increment (AUTO_INCREMENT) for each new row inserted into the table.

  • The aut_id, aut_name, country, and home_city columns are defined with their respective data types.

  • The home_city column is marked as NOT NULL, meaning a value must be provided for this column in each row.

  • The PRIMARY KEY constraint is applied to the id column, ensuring each value is unique and serving as the primary identifier for each row in the table.

MySQL PRIMARY KEY CONSTRAINT

Usually, a table has a column or combination of columns that contain values used to uniquely identify each row in the table.This column or combination of columns is called PRIMARY KEY and can be created by defining a PRIMARY KEY CONSTRAINT while creating a table. A table can have only one PRIMARY KEY. A PRIMARY KEY column cannot contain NULL values.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY set to the column aut_id.


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL
    aut_id varchar(8) NOT NULL,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL
    country varchar(25) NOT NULL,
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL,
    -- Define the primary key constraint on the "aut_id" column
    PRIMARY KEY (aut_id)
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The aut_id, aut_name, country, and home_city columns are defined with their respective data types and marked as NOT NULL, meaning values must be provided for these columns in each row.

  • The PRIMARY KEY constraint is applied to the aut_id column, ensuring each value is unique and serving as the primary identifier for each row in the table.

MySQL CREATE TABLE PRIMARY KEY CONSTRAINT on single column

In this topic, we have discussed how to set a PRIMARY KEY CONSTRAINT on a column of a table.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY set to the aut_id column. Notice that here PRIMARY KEY keyword is used within the column definition. 


CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL  PRIMARY KEY,	   
aut_name varchar(50) NOT NULL, 
country varchar(25) 
NOT NULL, 
home_city varchar(25) NOT NULL);

-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL and serves as the primary key
    aut_id varchar(8) NOT NULL PRIMARY KEY,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL
    country varchar(25) NOT NULL,
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The aut_id column is defined with data type VARCHAR(8) and marked as NOT NULL, meaning a value must be provided for this column in each row. It also serves as the primary key for the table.

  • The aut_name, country, and home_city columns are defined with their respective data types and marked as NOT NULL, meaning values must be provided for these columns in each row.

  • The PRIMARY KEY constraint is applied to the aut_id column, ensuring each value is unique and serving as the primary identifier for each row in the table.

MySQL CREATE TABLE PRIMARY KEY UNIQUE CONSTRAINT

In this topic, we have discussed how to set PRIMARY KEY as well as UNIQUE constraints on columns of a table while creating a table with CREATE TABLE command.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY is set to the aut_id column and UNIQUE is set to the home_city column. 


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL and serves as the primary key
    aut_id varchar(8) NOT NULL PRIMARY KEY,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL
    country varchar(25) NOT NULL,
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL and must be unique
    home_city varchar(25) NOT NULL UNIQUE
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The aut_id column is defined with data type VARCHAR(8) and marked as NOT NULL, meaning a value must be provided for this column in each row. It also serves as the primary key for the table.

  • The aut_name and country columns are defined with their respective data types and marked as NOT NULL, meaning values must be provided for these columns in each row.

  • The home_city column is defined with data type VARCHAR(25), marked as NOT NULL, and has a UNIQUE constraint, ensuring that each value in this column is unique.

  • The PRIMARY KEY constraint is applied to the aut_id column, ensuring each value is unique and serving as the primary identifier for each row in the table.

MySQL CREATE TABLE PRIMARY KEY on multiple columns

MySQL allows you to set PRIMARY KEY on multiple columns of a table. Doing this allows you to work on multiple columns as a single entity set as PRIMARY KEY for a table.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY is set with the combination of aut_id and home_city columns.


-- Create a new table if it does not already exist, named "newauthor"
CREATE TABLE IF NOT EXISTS newauthor
(
    -- Define a column named "aut_id" with data type VARCHAR(8) that cannot be NULL
    aut_id varchar(8) NOT NULL,
    -- Define a column named "aut_name" with data type VARCHAR(50) that cannot be NULL
    aut_name varchar(50) NOT NULL,
    -- Define a column named "country" with data type VARCHAR(25) that cannot be NULL
    country varchar(25) NOT NULL,
    -- Define a column named "home_city" with data type VARCHAR(25) that cannot be NULL
    home_city varchar(25) NOT NULL,
    -- Define the primary key constraint on the combination of "aut_id" and "home_city" columns
    PRIMARY KEY (aut_id, home_city)
);

Explanation:

  • This SQL script creates a new table called "newauthor" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The aut_id, aut_name, country, and home_city columns are defined with their respective data types and marked as NOT NULL, meaning values must be provided for these columns in each row.

  • The PRIMARY KEY constraint is applied to the combination of aut_id and home_city columns, ensuring each combination is unique and serving as the primary identifier for each row in the table. This means that the same aut_id can exist with different home_city, but the combination of aut_id and home_city must be unique.

MySQL creating table with FOREIGN KEY CONSTRAINT

While creating (or modifying) a MySQL table, you can set a FOREIGN KEY CONSTRAINT to a column of the table. A foreign key is a column or combination of columns which can be used to set a link between the data in two tables. PRIMARY KEY of a table is linked to the FOREIGN KEY of another table to enhance data integrity.

Syntax:

FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);

Arguments:

Name Description
column list A list of the columns on which FOREIGN KEY is to be set.
REFERENCES Keyword.
primary key table Table name which contains the PRIMARY KEY.
column list A list of the columns on which PRIMARY KEY is set in the primary key table.

Example:

If you want to do the following tasks:

A new table 'newbook_mast' will be created.

The PRIMARY KEY for that table 'newbook_mast' is 'book_id'.

The FOREIGN KEY for the table 'newbook_mast' is 'aut_id'.

The 'aut_id' is the PRIMARY KEY for the table 'newauthor'.

The FOREIGN KEY 'aut_id' for the table 'newbook_mast' points to the PRIMARY KEY 'aut_id' of the table 'newauthor'.

That means the 'aut_id's which are present in the 'newauthor' table, only those authors will come to the 'newbook_mast' table.

Here is the MySQL statement below for the above tasks. 


-- Create a new table if it does not already exist, named "newbook_mast"
CREATE TABLE IF NOT EXISTS newbook_mast 
(
    -- Define a column named "book_id" with data type VARCHAR(15) that cannot be NULL and serves as the primary key
    book_id varchar(15) NOT NULL PRIMARY KEY,
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "isbn_no" with data type VARCHAR(15) that cannot be NULL
    isbn_no varchar(15) NOT NULL,
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "aut_id" with data type VARCHAR(8)
    aut_id varchar(8),
    -- Define a column named "pub_id" with data type VARCHAR(8)
    pub_id varchar(8),
    -- Define a column named "dt_of_pub" with data type DATE
    dt_of_pub date,
    -- Define a column named "pub_lang" with data type VARCHAR(15)
    pub_lang varchar(15),
    -- Define a column named "no_page" with data type DECIMAL(5,0)
    no_page decimal(5,0),
    -- Define a column named "book_price" with data type DECIMAL(8,2)
    book_price decimal(8,2),
    -- Define a foreign key constraint on the "aut_id" column, referencing the "aut_id" column in the "newauthor" table
    FOREIGN KEY (aut_id) REFERENCES newauthor(aut_id)
);

Explanation:

  • This SQL script creates a new table called "newbook_mast" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The book_id column is defined with data type VARCHAR(15) and marked as NOT NULL, serving as the primary key for the table.

  • Other columns like book_name, isbn_no, cate_id, aut_id, pub_id, dt_of_pub, pub_lang, no_page, and book_price are defined with their respective data types.

  • The aut_id column is defined to be used as a foreign key, referencing the aut_id column in the newauthor table.

  • This foreign key constraint ensures referential integrity, meaning that values in the aut_id column of the newbook_mast table must exist in the aut_id column of the newauthor table.

MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple columns

MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple columns of a table. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced PRIMARY KEYs belonging to different tables.

Example:

If you want to do the following tasks:

A new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'.
That means the distinct ('ord_no' and 'book_id') combination which are present in the in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.

The another FOREIGN KEY 'cate_id' for the table 'newpurchase', which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.

Here is the MySQL statement below for the above tasks. 


-- Create a new table if it does not already exist, named "newpurchase"
CREATE TABLE IF NOT EXISTS newpurchase
(
    -- Define a column named "invoice_no" with data type VARCHAR(12) that cannot be NULL, must be unique, and serves as the primary key
    invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
    -- Define a column named "invoice_dt" with data type DATE
    invoice_dt date,
    -- Define a column named "ord_no" with data type VARCHAR(25)
    ord_no varchar(25),
    -- Define a column named "ord_date" with data type DATE
    ord_date date,
    -- Define a column named "receive_dt" with data type DATE
    receive_dt date,
    -- Define a column named "book_id" with data type VARCHAR(8)
    book_id varchar(8),
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "pub_lang" with data type VARCHAR(8)
    pub_lang varchar(8),
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "receive_qty" with data type INT(5)
    receive_qty int(5),
    -- Define a column named "purch_price" with data type DECIMAL(12,2)
    purch_price decimal(12,2),
    -- Define a column named "total_cost" with data type DECIMAL(12,2)
    total_cost decimal(12,2),
    -- Create an index on the combination of "ord_no" and "book_id" columns
    INDEX (ord_no,book_id),
    -- Define a foreign key constraint on the combination of "ord_no" and "book_id" columns, referencing the corresponding columns in the "neworder" table
    FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id),
    -- Create an index on the "cate_id" column
    INDEX (cate_id),
    -- Define a foreign key constraint on the "cate_id" column, referencing the "cate_id" column in the "category" table
    FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);

Explanation:

  • This SQL script creates a new table called "newpurchase" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The invoice_no column is defined with data type VARCHAR(12) and marked as NOT NULL, UNIQUE, and PRIMARY KEY, ensuring each value is unique and serving as the primary identifier for each row in the table.

  • Other columns like invoice_dt, ord_no, ord_date, receive_dt, book_id, book_name, pub_lang, cate_id, receive_qty, purch_price, and total_cost are defined with their respective data types.

  • Indexes are created on the combination of ord_no and book_id columns and the cate_id column to optimize query performance.

  • Foreign key constraints are applied to ensure referential integrity:

    • The combination of ord_no and book_id columns references the corresponding columns in the neworder table.

    • The cate_id column references the cate_id column in the category table.

MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple tables

MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple tables. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced to PRIMARY KEYs belonging to different tables.

Example:

If you want to do the following tasks :

Aa new table 'newbook_mast' will be created.
The PRIMARY KEY for that table 'newbook_mast' is 'book_id'.
The one FOREIGN KEY for the table 'newbook_mast' is 'aut_id'.
The another FOREIGN KEY for the table 'newbook_mast' is 'pub_id'.
The 'aut_id' is the PRIMARY KEY for the table 'newauthor'.
The 'pub_id' is the PRIMARY KEY for the table 'newpublisher'.
The FOREIGN KEY 'aut_id' for the table 'newbook_mast', which points to the PRIMARY KEY 'aut_id' of the table 'newauthor'.
That means the 'aut_id' which are present in the in the 'nuwauthor' table only those authors will come to the 'newbook_mast' table.
The another FOREIGN KEY 'pub_id' for the table 'newbook_mast' , which points to the PRIMARY KEY 'pub_id' of the table 'newpublisher'.
That means the 'pub_id' which are present in the in the 'newpublisher' table only those publishers will come to the 'newbook_mast' table.

Here is the MySQL statement below for the above tasks.


-- Create a new table if it does not already exist, named "newbook_mast"
CREATE TABLE IF NOT EXISTS newbook_mast
(
    -- Define a column named "book_id" with data type VARCHAR(15) that cannot be NULL and serves as the primary key
    book_id varchar(15) NOT NULL PRIMARY KEY,
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "isbn_no" with data type VARCHAR(15) that cannot be NULL
    isbn_no varchar(15) NOT NULL,
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "aut_id" with data type VARCHAR(8)
    aut_id varchar(8),
    -- Define a column named "pub_id" with data type VARCHAR(8)
    pub_id varchar(8),
    -- Define a column named "dt_of_pub" with data type DATE
    dt_of_pub date,
    -- Define a column named "pub_lang" with data type VARCHAR(15)
    pub_lang varchar(15),
    -- Define a column named "no_page" with data type DECIMAL(5,0)
    no_page decimal(5,0),
    -- Define a column named "book_price" with data type DECIMAL(8,2)
    book_price decimal(8,2),
    -- Create an index on the "aut_id" column
    INDEX (aut_id),
    -- Define a foreign key constraint on the "aut_id" column, referencing the "aut_id" column in the "newauthor" table
    FOREIGN KEY(aut_id) REFERENCES newauthor(aut_id),
    -- Create an index on the "pub_id" column
    INDEX(pub_id),
    -- Define a foreign key constraint on the "pub_id" column, referencing the "pub_id" column in the "newpublisher" table
    FOREIGN KEY(pub_id) REFERENCES newpublisher(pub_id)
);

Explanation:

  • This SQL script creates a new table called "newbook_mast" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The book_id column is defined with data type VARCHAR(15) and marked as NOT NULL and PRIMARY KEY, serving as the primary identifier for each row in the table.

  • Other columns like book_name, isbn_no, cate_id, aut_id, pub_id, dt_of_pub, pub_lang, no_page, and book_price are defined with their respective data types.

  • Indexes are created on the aut_id and pub_id columns to optimize query performance.

  • Foreign key constraints are applied to ensure referential integrity:

    • The aut_id column references the aut_id column in the newauthor table.

    • The pub_id column references the pub_id column in the newpublisher table.

MySQL CREATE TABLE with CASCADE and RESTRICT

MySQL allows creating a table with CASCADE and RESTRICT options.

CASCADE option deletes or updates the row from the parent table (containing PRIMARY KEYs), and automatically delete or update the matching rows in the child table (containing FOREIGN KEYs).

RESTRICT option bars the removal (i.e. using delete) or modification (i..e using an update) of rows from the parent table.

Example:

If you want to do the following tasks:

A new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.
The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
The ON UPDATE CASCADE ensures that the records inside the child table 'newpurchase' always points to the PRIMARY KEY inside the parent table 'neworder'.
If any record gets deleted/updated from the 'neworder' table MySQL handles the deletion/updating of the records from 'newpurchase' table.
ON DELETE RESTRICT prevents a record in a parent table 'neworder' being deleted or altered when it is still referenced from a child table 'newpurchase'.

Here is the MySQL statement below for the above tasks.


-- Create a new table if it does not already exist, named "newpurchase"
CREATE TABLE IF NOT EXISTS newpurchase
(
    -- Define a column named "invoice_no" with data type VARCHAR(12) that cannot be NULL, must be unique, and serves as the primary key
    invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
    -- Define a column named "invoice_dt" with data type DATE
    invoice_dt date,
    -- Define a column named "ord_no" with data type VARCHAR(25)
    ord_no varchar(25),
    -- Define a column named "ord_date" with data type DATE
    ord_date date,
    -- Define a column named "receive_dt" with data type DATE
    receive_dt date,
    -- Define a column named "book_id" with data type VARCHAR(8)
    book_id varchar(8),
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "pub_lang" with data type VARCHAR(8)
    pub_lang varchar(8),
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "receive_qty" with data type INT(5)
    receive_qty int(5),
    -- Define a column named "purch_price" with data type DECIMAL(12,2)
    purch_price decimal(12,2),
    -- Define a column named "total_cost" with data type DECIMAL(12,2)
    total_cost decimal(12,2),
    -- Create an index on the combination of "ord_no" and "book_id" columns
    INDEX (ord_no,book_id),
    -- Define a foreign key constraint on the combination of "ord_no" and "book_id" columns, 
    -- referencing the corresponding columns in the "neworder" table with cascade update and restrict delete behavior
    FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    -- Create an index on the "cate_id" column
    INDEX (cate_id),
    -- Define a foreign key constraint on the "cate_id" column, referencing the "cate_id" column in the "category" table
    FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);

Explanation:

  • This SQL script creates a new table called "newpurchase" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The invoice_no column is defined with data type VARCHAR(12) and marked as NOT NULL, UNIQUE, and PRIMARY KEY, ensuring each value is unique and serving as the primary identifier for each row in the table.

  • Other columns like invoice_dt, ord_no, ord_date, receive_dt, book_id, book_name, pub_lang, cate_id, receive_qty, purch_price, and total_cost are defined with their respective data types.

  • Indexes are created on the combination of ord_no and book_id columns and the cate_id column to optimize query performance.

  • Foreign key constraints are applied to ensure referential integrity:

    • The combination of ord_no and book_id columns references the corresponding columns in the neworder table with cascade update and restrict delete behavior.

    • The cate_id column references the cate_id column in the category table.

MySQL CREATE TABLE with SET NULL

MySQL allows you to create a table with SET NULL option. Doing so will delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.

You can use SET NULL for DELETE as well as UPDATE.

If SET NULL is used, you should not set NOT NULL options to the columns of the child table (containing FOREIGN KEYS).

Example:

If you want to do the following tasks:

Aa new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.

The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
ON UPDATE SET NULL recurses to update the 'newpurchase' table it has already updated during the cascade, it acts like RESTRICT. It prevents infinite loops resulting from cascaded updates.

Here is the MySQL statement below for the above tasks.



-- Create a new table if it does not already exist, named "newpurchase"
CREATE TABLE IF NOT EXISTS newpurchase
(
    -- Define a column named "invoice_no" with data type VARCHAR(12) that cannot be NULL, must be unique, and serves as the primary key
    invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
    -- Define a column named "invoice_dt" with data type DATE
    invoice_dt date,
    -- Define a column named "ord_no" with data type VARCHAR(25)
    ord_no varchar(25),
    -- Define a column named "ord_date" with data type DATE
    ord_date date,
    -- Define a column named "receive_dt" with data type DATE
    receive_dt date,
    -- Define a column named "book_id" with data type VARCHAR(8)
    book_id varchar(8),
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "pub_lang" with data type VARCHAR(8)
    pub_lang varchar(8),
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "receive_qty" with data type INT(5)
    receive_qty int(5),
    -- Define a column named "purch_price" with data type DECIMAL(12,2)
    purch_price decimal(12,2),
    -- Define a column named "total_cost" with data type DECIMAL(12,2)
    total_cost decimal(12,2),
    -- Create an index on the combination of "ord_no" and "book_id" columns
    INDEX (ord_no,book_id),
    -- Define a foreign key constraint on the combination of "ord_no" and "book_id" columns, 
    -- referencing the corresponding columns in the "neworder" table with cascade update and set null delete behavior
    FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id) ON UPDATE CASCADE ON DELETE SET NULL,
    -- Create an index on the "cate_id" column
    INDEX (cate_id),
    -- Define a foreign key constraint on the "cate_id" column, referencing the "cate_id" column in the "category" table
    FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);

Explanation:

  • This SQL script creates a new table called "newpurchase" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The invoice_no column is defined with data type VARCHAR(12) and marked as NOT NULL, UNIQUE, and PRIMARY KEY, ensuring each value is unique and serving as the primary identifier for each row in the table.

  • Other columns like invoice_dt, ord_no, ord_date, receive_dt, book_id, book_name, pub_lang, cate_id, receive_qty, purch_price, and total_cost are defined with their respective data types.

  • Indexes are created on the combination of ord_no and book_id columns and the cate_id column to optimize query performance.

  • Foreign key constraints are applied to ensure referential integrity:

    • The combination of ord_no and book_id columns references the corresponding columns in the neworder table with cascade update and set null delete behavior.

    • The cate_id column references the cate_id column in the category table.

MySQL CREATE TABLE with NO ACTION

NO ACTION option in MySQL is equivalent to RESTRICT.

Example:

If you want to do the following tasks:

Aa new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.
The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
The ON DELETE NO ACTION are preventing a record in a parent 'neworder' being deleted or altered when it is still referenced from a child table 'newpurchase' .

Here is the MySQL statement below for the above tasks.


-- Create a new table if it does not already exist, named "newpurchase"
CREATE TABLE IF NOT EXISTS newpurchase
(
    -- Define a column named "invoice_no" with data type VARCHAR(12) that cannot be NULL, must be unique, and serves as the primary key
    invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
    -- Define a column named "invoice_dt" with data type DATE
    invoice_dt date,
    -- Define a column named "ord_no" with data type VARCHAR(25)
    ord_no varchar(25),
    -- Define a column named "ord_date" with data type DATE
    ord_date date,
    -- Define a column named "receive_dt" with data type DATE
    receive_dt date,
    -- Define a column named "book_id" with data type VARCHAR(8)
    book_id varchar(8),
    -- Define a column named "book_name" with data type VARCHAR(50)
    book_name varchar(50),
    -- Define a column named "pub_lang" with data type VARCHAR(8)
    pub_lang varchar(8),
    -- Define a column named "cate_id" with data type VARCHAR(8)
    cate_id varchar(8),
    -- Define a column named "receive_qty" with data type INT(5)
    receive_qty int(5),
    -- Define a column named "purch_price" with data type DECIMAL(12,2)
    purch_price decimal(12,2),
    -- Define a column named "total_cost" with data type DECIMAL(12,2)
    total_cost decimal(12,2),
    -- Create an index on the combination of "ord_no" and "book_id" columns
    INDEX (ord_no,book_id),
    -- Define a foreign key constraint on the combination of "ord_no" and "book_id" columns, 
    -- referencing the corresponding columns in the "neworder" table with cascade update and no action delete behavior
    FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id) ON UPDATE CASCADE ON DELETE NO ACTION,
    -- Create an index on the "cate_id" column
    INDEX (cate_id),
    -- Define a foreign key constraint on the "cate_id" column, referencing the "cate_id" column in the "category" table
    FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);

Explanation:

  • This SQL script creates a new table called "newpurchase" in a MySQL database.

  • The table is created if it doesn't already exist, ensuring that the script won't produce an error if the table is already present.

  • Each line of code within the CREATE TABLE statement defines a column or constraint in the table.

  • The invoice_no column is defined with data type VARCHAR(12) and marked as NOT NULL, UNIQUE, and PRIMARY KEY, ensuring each value is unique and serving as the primary identifier for each row in the table.

  • Other columns like invoice_dt, ord_no, ord_date, receive_dt, book_id, book_name, pub_lang, cate_id, receive_qty, purch_price, and total_cost are defined with their respective data types.

  • Indexes are created on the combination of ord_no and book_id columns and the cate_id column to optimize query performance.

  • Foreign key constraints are applied to ensure referential integrity:

    • The combination of ord_no and book_id columns references the corresponding columns in the neworder table with cascade update and no action delete behavior.

    • The cate_id column references the cate_id column in the category table.

Previous: Loading data into a table and usage of line terminator
Next: MySQL CREATE INDEX



Follow us on Facebook and Twitter for latest update.