Thursday, August 10, 2023

UNIQUE Constraint on ALTER TABLE

UNIQUE Constraint on ALTER TABLE

Continuation of last blog…

To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
DROP INDEX UC_Person;

MySQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Wednesday, August 9, 2023

MySQL NOT NULL Constraint

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

NOT NULL on CREATE TABLE

The following SQL ensures that the “ID”, “LastName”, and “FirstName” columns will NOT accept NULL values when the “Persons” table is created:

Example

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:

Example

ALTER TABLE Persons
MODIFY Age int NOT NULL;

MySQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

Tuesday, August 8, 2023

The constraint in MySQL is used to specify the rule that allows or restricts what values/data will be stored in the table. They provide a suitable method to ensure data accuracy and integrity inside the table. It also helps to limit the type of data that will be inserted inside the table. If any interruption occurs between the constraint and data action, the action is failed.

Types of MySQL Constraints

Constraints in MySQL is classified into two types:

Column Level Constraints: These constraints are applied only to the single column that limits the type of particular column data.
Table Level Constraints: These constraints are applied to the entire table that limits the type of data for the whole table.

How to create constraints in MySQL

We can define the constraints during a table created by using the CREATE TABLE statement. MySQL also uses the ALTER TABLE statement to specify the constraints in the case of the existing table schema.

Syntax

The following are the syntax to create a constraints in table:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

Constraints used in MySQL

The following are the most common constraints used in the MySQL:

NOT NULL
CHECK
DEFAULT
PRIMARY KEY
AUTO_INCREMENT
UNIQUE
INDEX
ENUM
FOREIGN KEY

Let us discuss each of these constraints in detail.

In this blog let us discuss only 3 constraints and remaining in next blog

Not NULL CONSTRAINT
UNIQUE CONSTRAINT
CHECK CONSTRAINT

NOT NULL Constraint

This constraint specifies that the column cannot have NULL or empty values. The below statement creates a table with NOT NULL constraint.

mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));

Execute the queries listed below to understand how it works:

mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');  
  
mysql> INSERT INTO Student VALUES(2, 'peter', 'Amanda', 'Florida');

UNIQUE Constraint

This constraint ensures that all values inserted into the column will be unique. It means a column cannot stores duplicate values. MySQL allows us to use more than one column with UNIQUE constraint in a table. The below statement creates a table with a UNIQUE constraint:

mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));

Execute the queries listed below to understand how it works:

mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantaloons', 38), (2, 'Cantabil', 40);  
  
mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymond', 38), (2, 'xxx', 40);

CHECK Constraint

It controls the value in a particular column. It ensures that the inserted value in a column must be satisfied with the given condition. In other words, it determines whether the value associated with the column is valid or not with the given condition.

Syntax:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

mysql> CREATE TABLE Persons (  
    ID int NOT NULL,  
    Name varchar(45) NOT NULL,  
    Age int CHECK (Age>=18)  
);

mysql> INSERT INTO Persons(Id, Name, Age)   
VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);  
  
mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);

As first statements executes successfully but the second statement does not executes as it violates the syntax.

Monday, August 7, 2023

MySQL ALTER TABLE Statement

MySQL ALTER TABLE Statement

MySQL ALTER statement is used when you want to change the name of your table or any table field. It is also used to add or delete an existing column in a table.

The ALTER statement is always used with “ADD”, “DROP” and “MODIFY” commands according to the situation.

1) ADD a column in the table

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following SQL adds an “Email” column to the “Customers” table:

Example

ALTER TABLE Customers
ADD Email varchar(255);

2) Add multiple columns in the table

Syntax:

 ALTER TABLE table_name  
 ADD new_column_name column_definition  
 [ FIRST | AFTER column_name ],  
ADD new_column_name column_definition  
[ FIRST | AFTER column_name ],  
  ...  
;

Example:

In this example, we add two new columns “Contact”, and City in the existing table “Customers”. cus_contact is added after cus_surname column and cus_salary is added after cus_City column.

ALTER TABLE Customer
ADD cus_contact varchar(100) NOT NULL  
AFTER cus_surname,  
ADD cus_salary int(100) NOT NULL  
AFTER cus_city;

3) ALTER TABLE — DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

The following SQL deletes the “Email” column from the “Customers” table:

Example

ALTER TABLE Customers
DROP COLUMN Email;

4) ALTER TABLE — MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

MySQL ALTER TABLE Example

Look at the “Customers” table:

Now we want to add a column named “ContactNumber” in the “Customers” table.

We use the following SQL statement:

Example

ALTER TABLE Customers
ADD ContactNumber int(10);

Notice that the new column, “ContactNumber”, is of type int and is going to hold a number. The data type specifies what type of data the column can hold.

5) Change Data Type Example

Now we want to change the data type of the column named “PostalCode” in the “Customers” table.

We use the following SQL statement:

Example

ALTER TABLE Customers
MODIFY COLUMN PostalCode int(10);

6) DROP COLUMN Example

Next, we want to delete the column named “ContactNumber” in the “Customers” table.

We use the following SQL statement:

Example

ALTER TABLE Customers
DROP COLUMN ContactNumber;

Sunday, August 6, 2023

MySQL DROP TABLE Statement

MySQL DROP TABLE Statement

MYSQL uses a Drop Table statement to delete the existing table. This statement removes the complete data of a table along with the whole structure or definition permanently from the database. So, you must be very careful while removing the table because we cannot recover the lost data after deleting it.

Syntax

The following are the syntax to remove the table in MySQL:

DROP TABLE table_name;

MySQL DROP TABLE Example

The following SQL statement drops the existing table “Customers”:

Example

DROP TABLE Customers;

MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax

TRUNCATE TABLE table_name;

MySQL TRUNCATE TABLE Example

The following SQL statement truncate the existing table “Customers”:

Example

TRUNCATE TABLE Customers;

MySQL TRUNCATE Table vs. DROP Table

You can also use the DROP TABLE command to delete the complete table, but it will remove complete table data and structure both. You need to re-create the table again if you have to store some data. But in the case of TRUNCATE TABLE, it removes only table data, not structure. You don’t need to re-create the table again because the table structure already exists.