Your cart is currently empty!
Author: alien
-
Khóa học miễn phí MySQL – Unique Key nhận dự án làm có lương
MySQL – Unique Key
Table of content
A Unique Key in MySQL, when applied on a certain field of a database table, does not allow duplicate values to be inserted in that column, i.e. it is used to uniquely identify a record in a table.
Usually, any relational database contains a lot of information stored in multiple tables and each table holds a huge number of records. When we are handling such huge amounts of data there is a chance of redundancy (duplicate records). SQL keys are a way to handle this issue.
This Unique Key works as an alternative to the Primary Key constraint; as both unique and primary keys assure uniqueness in a column of a database table.
Creating MySQL Unique Key
We can create a Unique Key on a MySQL table column using the UNIQUE keyword, and it holds the following features −
- Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key.
- It cannot have duplicate values.
- It can also be used as a foreign key in another table.
- A table can have more than one Unique column.
Syntax
Following is the syntax to create a UNIQUE key constraint on a column in a table −
CREATE TABLE table_name( column_name1 datatype UNIQUE, column_name2 datatype, ... );
As you observe, we just need to specify the keyword UNIQUE after the name of the desired column while creating a table using CREATE TABLE statement.
Example
In this example, let us create a table named CUSTOMERS and define a UNIQUE Key on one of its fields, ADDRESS. Look at the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2) );
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES UNI NULL SALARY decimal(18, 2) YES NULL As you can see in the table definition, the Unique Key is created on the ADDRESS field.
Creating Multiple Unique Keys
We can create one or more Unique Key constraints on a column in a single MySQL table. When this constraint is applied in multiple fields, one cannot insert duplicate values in those fields.
Syntax
Following is the syntax to create unique key constraints on multiple columns in a table −
CREATE TABLE table_name(column_name1 UNIQUE, column_name2 UNIQUE,...)
Example
Assume we have created another table with the name CUSTOMERS in the MySQL database using CREATE TABLE statement.
Here we are creating a UNIQUE constraint on columns NAME and ADDRESS using the UNIQUE keyword as shown below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL UNIQUE, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2) );
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO UNI NULL AGE int NO NULL ADDRESS char(25) YES UNI NULL SALARY decimal(18, 2) YES NULL Creating Unique Key on Existing Columns
We can add a unique key constraint on an existing column of a table using the ALTER TABLE… ADD CONSTRAINT statement.
Syntax
Following is the syntax to create a UNIQUE Key on existing columns of a table −
ALTER TABLE table_name ADD CONSTRAINT unique_key_name UNIQUE (column_name);
Note − Here the UNIQUE_KEY_NAME is just the name of the Unique Key. It is optional to specify the name while creating a unique key. It is used to drop the constraint from the column in a table.
Example
Using the ALTER TABLE statement, you can add a UNIQUE constraint on any existing column in the CUSTOMERS table created previously. In the following example, we are applying the UNIQUE constraint on the NAME column as shown below −
ALTER TABLE CUSTOMERS ADD CONSTRAINT UNIQUE_NAME UNIQUE (NAME);
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO UNI NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Dropping MySQL Unique Key
If there is an unique constraint on a column already, you can drop it whenever it is not needed. To drop the Unique Constraint from the column of a table you need to use the ALTER TABLE statement again.
Syntax
Following is the SQL query to drop the UNIQUE constraint from the column of a table −
ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE_KEY_NAME;
Example
In this example, we will drop the constraint named UNIQUE_NAME from the column NAME of the CUSTOMERS table using the following MySQL query −
ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_NAME;
Output
The table structure displayed will contain a UNI index only on the ADDRESS column, referring that the index on NAME column is removed.
Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Creating Unique Key Using Client Program
In addition to use a key on a column to identify uniquely using the MySQL query We can also apply a Unique Key constraint on a Field using a client program.
Syntax
To apply unique key on a table field through a PHP program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the mysqli function query() as follows −
$sql = ''CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30)) $mysqli->query($sql);
To apply unique key on a table field through a JavaScript program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the query() function of mysql2 library as follows −
sql = "CREATE TABLE customers(cust_ID INT NOT NULL, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT, PRIMARY KEY(cust_login_ID))"; con.query(sql);
To apply unique key on a table field through a Java program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the JDBC function execute() as follows −
String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))"; statement.execute(sql);
To apply unique key on a table field through a python program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the execute() function of the MySQL Connector/Python as follows −
unique_key_query = ''CREATE TABLE TEST1 (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, MOBILE BIGINT UNIQUE, AADHAR BIGINT UNIQUE, AGE INT NOT NULL)'' cursorObj.execute(unique_key_query)
Example
Following are the programs −
$dbhost = ''localhost $dbuser = ''root $dbpass = ''password $dbname = ''TUTORIALS $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf(''Connected successfully.
''); $sql = ''CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30)) if ($mysqli->query($sql)) { echo "Unique column created successfully in customers table n"; } if ($mysqli->errno) { printf("Table could not be created!.
", $mysqli->error); } $mysqli->close();Output
The output obtained is as follows −
Unique column created successfully in customers table
var mysql = require("mysql2"); var con = mysql.createConnection({ host: "localhost", user: "root", password: "password", }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected successfully...!"); console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); //create a table that stores primary key! sql = "CREATE TABLE customers(cust_ID INT NOT NULL, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT, PRIMARY KEY(cust_login_ID))"; con.query(sql); //describe table details sql = "DESCRIBE TABLE customers"; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); });Output
The output produced is as follows −
[ { id: 1, select_type: ''SIMPLE'', table: ''customers'', partitions: null, type: ''ALL'', possible_keys: null, key: null, key_len: null, ref: null, rows: 1, filtered: 100, Extra: null } ]import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class UniqueKey { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Create a unique key in the customer table...!; String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))"; statement.execute(sql); System.out.println("Unique key created successfully...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE customers"); while (resultSet.next()){ System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" " +resultSet.getString(3)+ " "+ resultSet.getString(4)); } connection.close(); } catch (Exception e) { System.out.println(e); } } }Output
The output obtained is as shown below −
Connected successfully...! Unique key created successfully...! Cust_ID int NO PRI null Cust_Name varchar(30) YES null
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) cursorObj = connection.cursor() # Create table unique_key_query = ''CREATE TABLE TEST1 (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, MOBILE BIGINT UNIQUE, AADHAR BIGINT UNIQUE, AGE INT NOT NULL)'' cursorObj.execute(unique_key_query) connection.commit() print("Unique key column is created successfully!") cursorObj.close() connection.close()Output
Following is the output of the above code −
Unique key column is created successfully!
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí MySQL – Delete Join nhận dự án làm có lương
MySQL – Delete Join
Simple deletion operation in MySQL can be performed on a single entity or multiple entities of a table. But what if this deletion operation is to be performed on multiple entities of multiple tables? This is where Joins come into picture.
MySQL DELETE… JOIN
As we have discussed in this tutorial previously, Joins are used to retrieve records from two or more tables, by combining columns of these tables based on the common fields. This merged data can be deleted with all the changes reflected in original tables.
Syntax
Following is the basic syntax of DELETE… JOIN statement in MySQL −
DELETE table(s) FROM table1 JOIN table2 ON table1.common_field = table2.common_field;
We can use any join clause (INNER JOIN, LEFT JOIN, RIGHT JOIN etc.) while performing deletion.
Example
In this example, we first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00), (2, ''Khilan'', 25, ''Delhi'', 1500.00), (3, ''Kaushik'', 23, ''Kota'', 2000.00), (4, ''Chaitali'', 25, ''Mumbai'', 6500.00), (5, ''Hardik'', 27, ''Bhopal'', 8500.00), (6, ''Komal'', 22, ''Hyderabad'', 4500.00), (7, ''Muffy'', 24, ''Indore'', 10000.00);
The table will be created as −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
Let us create another table ORDERS, containing the details of orders made and the date they are made on.
CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2), );
Using the INSERT statement, insert values into this table as follows −
INSERT INTO ORDERS VALUES (102, ''2009-10-08 00:00:00'', 3, 3000.00), (100, ''2009-10-08 00:00:00'', 3, 1500.00), (101, ''2009-11-20 00:00:00'', 2, 1560.00), (103, ''2008-05-20 00:00:00'', 4, 2060.00);
The table is displayed as follows −
| OID | DATE | CUSTOMER_ID | AMOUNT |
|---|---|---|---|
| 102 | 2009-10-08 00:00:00 | 3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060.00 |
The delete operation is performed by applying the DELETE… JOIN query on these tables.
DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID;
Verification
To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
DELETE… JOIN with WHERE Clause
The ON clause in DELETE… JOIN query is used to apply constraints on the records. In addition to it, we can also use WHERE clause to make the filtration stricter. Observe the query below; here, we are trying to delete the records of customers, in the CUSTOMERS table, whose salary is lower than Rs. 2000.00.
DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID WHERE a.SALARY < 2000.00;
Verification
To verify whether the changes are reflected in the original tables or not, we will use the SELECT statement.
The CUSTOMERS table after deletion is as follows −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
Delete Join Using Client Program
In addition to joining two or more than two tables using the MySQL query, we can also perform the Delete Join operation using a client program.
Syntax
To perform Delete Join through a PHP program, we need to execute the DELETE statement with JOIN clause using the mysqli function query() as follows −
$sql = ''DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author $mysqli->query($sql);
To perform Delete Join through a JavaScript program, we need to execute the DELETE statement with JOIN clause using the query() function of mysql2 library as follows −
sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author"; con.query(sql);
To perform Delete Join through a Java program, we need to execute the DELETE statement with JOIN clause using the JDBC function executeUpdate() as follows −
String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author"; statement.executeUpdate(sql);
To perform Delete Join through a python program, we need to execute the DELETE statement with JOIN clause using the execute() function of the MySQL Connector/Python as follows −
delete_join_query = "DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID" cursorObj.execute(delete_join_query)
Example
Following are the programs −
$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
// printf(''Connected successfully.
'');
$sql = ''DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
if ($mysqli->query($sql)) {
printf("Join deleted successfully!.
");
}
if ($mysqli->errno) {
printf("Join could not be deleted !.
", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows −
Join deleted successfully!.
var mysql = require("mysql2");
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
}); //Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
// console.log("Connected successfully...!");
// console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
//Delete Join
sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});
Output
The output produced is as follows −
ResultSetHeader {
fieldCount: 0,
affectedRows: 2,
insertId: 0,
info: '''',
serverStatus: 34,
warningStatus: 0,
changedRows: 0
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DeleteJoin {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String username = "root";
String password = "password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("Connected successfully...!");
//MySQL Delete JOIN...!;
String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
statement.executeUpdate(sql);
System.out.println("JOIN Deleted successfully...!");
connection.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
Output
The output obtained is as shown below −
Connected successfully...! JOIN Deleted successfully...!
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
host=''localhost'',
user=''root'',
password=''password'',
database=''tut''
)
cursorObj = connection.cursor()
delete_join_query = f"""DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID"""
cursorObj.execute(delete_join_query)
connection.commit()
print("deleted succesfully")
cursorObj.close()
connection.close()
Output
Following is the output of the above code −
deleted successfully
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
