MySQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins.

MySQL Full Join

In MySQL, there is no provision to perform full join operation. We can, however, imitate this operation to produce the same results.

The result-set obtained from performing full join is a union of result-sets obtained from left join and right join. Thus, we can first retrieve result-sets from left and right join operations and combine them using the UNION keyword.

But, this method only works for cases where duplicate records are non-existent. If we want to include the duplicate rows, using UNION ALL keyword to combine the result-sets is preferred.

Syntax

Following is the basic syntax to emulate Full Join −

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field

[UNION | UNION ALL]

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Example

In this example, we are imitating the full join operation using UNION or UNION ALL keyword. First, let us create a table named CUSTOMERS using the following query −;

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 −

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 −

Full Join Query −

On executing the following query, we will produce the union of two tables CUSTOMERS and ORDERS.

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

The resultant table is produced as follows −

Full Join with WHERE Clause

With Joins, we are filtering records using the ON clause, by default. Let us suppose there is a further requirement to filter records based on a certain condition, we can make use of WHERE clause with the Joins.

Syntax

The syntax of Full Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause.

SELECT ID, NAME, DATE, AMOUNT
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00

UNION

SELECT ID, NAME, DATE, AMOUNT
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

The resultant table after applying the where clause with full join contains the rows that has amount values greater than 2000.00 −

To update the data entered in a single database table using MySQL, you can use the UPDATE statement. However, to update the data in multiple database tables, we use the UPDATE… JOIN statement.

MySQL UPDATE… JOIN

Usually, JOINS in MySQL are used to fetch the combination of rows from multiple tables, with respect to a matching field. And since the UPDATE statement only modifies the data in a single table, we combine multiple tables into one using JOINS and then update them. This is also known as cross-table modification.

Syntax

Following is the basic syntax of the UPDATE… JOIN statement −

UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1
JOIN table2 ON column3 = column4;

Example

Let us 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 CUSTOMERS table will be created as −

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 ORDERS table is displayed as follows −

Use the following UPDATE… JOIN query to cross-modify multiple tables (CUSTOMERS and ORDERS) −

UPDATE CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000;

Verification

As we can see in the CUSTOMERS table below, the changes we have performed in the above query are reflected −

UPDATE… JOIN with WHERE Clause

The ON clause in UPDATE… JOIN query is used to apply constraints on the records to be updated. In addition to it, we can also use WHERE clause to make the constraints stricter. The syntax of it is as follows −

UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1
JOIN table2 ON column3 = column4
WHERE condition;

Example

Observe the query below. Here, we are trying to increase the salary of CUSTOMERS who only earn 2000.00 −

UPDATE CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET SALARY = SALARY + 1000
WHERE CUSTOMERS.SALARY = 2000.00;

Verification

As we can see in the CUSTOMERS table below, the CUSTOMERS who are earning the salary of 2000 got an increment of 1000 −

Update Join Using Client Program

In addition to join two or more tables using the MySQL query, we can also perform the update join operation using a client program.

Syntax

$sql = ''UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100
$mysqli->query($sql);

sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";
con.query(sql);

String sql = "UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100";
statement.executeUpdate(sql);

update_join_query = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"
cursorObj.execute(update_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();
}
// Updating Join
$sql = ''UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100
if ($mysqli->query($sql)) {
    echo "Join updated successfully! n";
} else {
    echo "Join could not be updated! n";
}

// Selecting the updated value
$update_res = ''SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author

$result = $mysqli->query($update_res);

if ($result->num_rows > 0) {
    echo "Updated tutorial_count! n";
    while ($row = $result->fetch_assoc()) {
        printf("Author: %s, Count: %d", $row["tutorial_author"], $row["tutorial_count"]);
        printf("
");
    }
} else {
    printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Join updated successfully!
Updated tutorial_count!
Author: John Paul, Count: 101
Author: Sanjay, Count: 101

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);

  //UPDATE JOIN
  sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";

  //displaying the updated data along with ID;
  sql =
    "SELECT c.ID, c.SALARY FROM Customers c INNER JOIN Orders o  ON c.ID = o.CUSTOMER_ID";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
  { ID: 3, SALARY: ''5000.00'' },
  { ID: 3, SALARY: ''5000.00'' },
  { ID: 2, SALARY: ''3060.00'' },
  { ID: 4, SALARY: ''8560.00'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class UpdateJoin {
   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 Update JOIN...!;
         String sql = "UPDATE tcount_tbln" +
         "INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_authorn" +
         "SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100n";
         statement.executeUpdate(sql);
         System.out.println("tutorial_count updated successfully...!");

         //fetch the records...!;
         ResultSet resultSet = statement.executeQuery("SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getInt(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
tutorial_count updated successfully...!
John Paul 201
Sanjay 201

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
update_join_query = f"""UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"""
cursorObj.execute(update_join_query)
connection.commit()
print("updated successfully")
cursorObj.close()
connection.close()

updated successfully

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 −

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 −

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.

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 −

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

$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);

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);

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);

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();

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);
  });
});

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);
      }
   }
}

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()

deleted successfully

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 −

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 −

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 −

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.

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

$sql = ''CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))
$mysqli->query($sql);

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);

String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))";
statement.execute(sql);

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();

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);
  });
});

[
    {
      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);
      }
   }
}

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()

Unique key column is created successfully!

MySQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables.

Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables.

Working of UNION

UNION is a type of operator/clause in MySQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.

The tables are said to be union compatible if they follow the conditions given below:

• The tables to be combined must have same number of columns with the same datatype.
• The number of rows need not be same.

Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table.

Note: Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type.

Syntax

Following is the syntax of UNION operator in MySQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two table “COURSES_PICKED” and “EXTRA_COURSES_PICKED” with the same number of columns having same data types.

Create table COURSES_PICKED using the following query −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

Create table EXTRA_COURSES_PICKED using the following query −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''PHOTOGRAPHY'');

Now, let us combine both these tables using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is −

Working of JOIN

The Join operation is used to combine information from multiple related tables into one, based on their common fields.

In this operation, every row of the first table will be combined with every row of the second table. The resultant table obtained will contain the rows present in both tables. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc.

There are two types of Joins:

• Inner Join
• Outer Join

The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join. Other joins like Cross join, Natural Join, Condition Join etc. are types of Inner Joins.

Outer join includes both matched and unmatched rows from the first table, in the resultant table. It is divided into subtypes like Left Join, Right Join, and Full Join.

Even though the join operation can merge multiple tables, the simplest way of joining two tables is without using any Clauses other than the ON clause.

Syntax

Following is the basic syntax of Join operation −

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.common_field = table2.common_field;

Example

In the following example, we will try to join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below −

mysql> SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME,
COURSES_PICKED FROM COURSES_PICKED c JOIN EXTRA_COURSES_PICKED e
ON c.STUDENT_ID = e.STUDENT_ID;

Output

The resultant table will be displayed as follows −

UNION vs JOIN

As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related.

Let us summarize all the difference between these queries below −

A PRIMARY KEY is a constraint applied on a field of a MySQL table. When this is applied, the values in that particular table column are uniquely identified. It is the most appropriate candidate key to be the main key of any table.

A table can have only one PRIMARY KEY, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a Composite Key.

You can either create a primary key while creating a new table or you can apply it on an already existing table in the database. But if it is being applied on an existing table, you must make sure that the table does not already contain a primary key and .

Creating MySQL Primary Key

To create a primary key on a new MySQL table, you must specify the column as the PRIMARY KEY while creating a new table using the CREATE TABLE statement.

Following are some points to remember while creating a Primary Key on a table −

• The Primary Key column must only contain unique values.
• It can not hold NULL values.
• One table can have only one Primary Key.
• A Primary Key length cannot be more than 900 bytes.

Syntax

Following is the syntax to define a column of a table as a primary key −

CREATE TABLE table_name(
   column_name NOT NULL PRIMARY KEY(column_name)
);

Example

In the following example, let us create a table with the name CUSTOMERS in a MySQL database using the CREATE TABLE query. In this query, we will add the PRIMARY KEY constraint on a column named ID.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) UNIQUE,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Verification

To verify further that the PRIMARY KEY constraint is applied on the ID column, let us insert different types of values into the CUSTOMERS table using the following queries −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 23, ''Pune'', 2000.00),
(1, ''John'', 25, ''Hyderabad'', 3000.00);

Following error is displayed −

ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.PRIMARY''

As we can see above, you cannot insert duplicate and null values into this primary key column.

Creating Primary Key on Existing Column

We can also add a primary key on an existing column of a table, if it was not created (for any reason) while creating a new table. However, adding a primary key on an existing table is only possible if the table does not already contain a primary key (as a MySQL table must not contain multiple primary keys), and the column it is being applied on must only contain unique values.

You can add the primary key on an existing table using the ALTER TABLE… ADD CONSTRAINT statement.

Syntax

Following is the syntax to create a unique constraint on existing columns of a table −

ALTER TABLE table_name
ADD CONSTRAINT
PRIMARY KEY (column_name);

Example

Using the ALTER TABLE statement, you can add a PRIMARY KEY on an existing column in the CUSTOMERS table created previously. In the following example, we are applying the PRIMARY KEY on the ID column as shown below −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT
PRIMARY KEY (ADDRESS);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

But if the column, on which the PRIMARY KEY is added, contains duplicate or null values, it cannot be set as a primary key.

Dropping MySQL Primary Key

MySQL provides the ALTER TABLE… DROP statement to drop the primary key from a table.

Syntax

Following is the syntax to drop the PRIMARY KEY constraint using the ALTER TABLE… DROP statement −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Let us consider the CUSTOMERS table with the primary key constraint present on a column named ID. You can drop this constraint from the column ID by executing the following statement

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Creating Primary Key Using Client Program

We can also apply a Primary Key on a table field using a client program.

Syntax

$sql = ''CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)
$mysqli->query($sql);

sql = `CREATE TABLE customers(cust_ID INT NOT NULL primary key, cust_Name VARCHAR(30))`;
con.query(sql);

String sql = "CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)";
statement.execute(sql);

primary_key_query = ''CREATE TABLE TEST (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID))''
cursorObj.execute(primary_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), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)
if ($mysqli->query($sql)) {
    echo "Primary key column created successfully in customers table n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

Primary key 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 stored primary key
  sql = `CREATE TABLE customers(cust_ID INT NOT NULL primary key, cust_Name VARCHAR(30))`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
    {
      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 PrimaryKey {
   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 primary key in the customer table...!;
         String sql = "CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)";
         statement.execute(sql);
         System.out.println("Primary 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);
      }
   }
}

Connected successfully...!
Primary key created successfully...!
cust_ID int NO UNI
cust_Name varchar(30) YES
cust_login_ID int NO PRI

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Create table
primary_key_query = ''''''CREATE TABLE TEST (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID))''''''
cursorObj.execute(primary_key_query)
connection.commit()
print("Primary key column is created successfully!")
cursorObj.close()
connection.close()

Primary key column is created successfully!

An Alternate Key in a table is nothing but an alternative to the primary key in that table. In other words, they are candidate keys that are not currently selected as the primary key of a table (but has a potential to be one). Hence, they can also be used to uniquely identify a tuple(or a record) in a table.

If a table consists of only one Candidate key that is treated as the primary key of the table, then there is no alternate key in that table.

There is no specific query or syntax to set the alternate key in a table. It is just an ordinary column that is a secondary candidate to be selected as a primary key.

Features of Alternate Keys

Even though alternate keys are not primary keys, they contain some important properties/features of their own. They are listed below −

• The alternate key does not allow duplicate values.
• A table can have more than one alternate keys.
• The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly.
• All alternate keys can be candidate keys, but all candidate keys can not be alternate keys. As a primary key, which is also a candidate key, can not be considered as an alternate key.

Types of Keys in a Table

Below is the list of keys that are present in a table −

• Candidate key
• Primary key
• Alternate key
• Foreign Key

Candidate Key

A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can either be a single field or multiple fields. Primary keys, alternate keys, foreign keys in a table are all types of candidate key.

Primary Key

A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table.

It can be set using the PRIMARY KEY keyword while creating a table using the CREATE TABLE statement. The syntax is as follows −

CREATE TABLE table_name(
   COLUMN_NAME1 datatype,
   COLUMN_NAME2 datatype,
   ...
   PRIMARY KEY(COLUMN_NAME)
);

Alternate Key

An Alternate key is a Candidate key that could be a primary key but is not. Like primary key, it also uniquely identifies the records in a field of a table to retrieve row tuples from the said table. There can be a single or multiple fields identifying as alternate keys in a table.

There is no syntax to set an alternate key in a database table.

Foreign Key

The Primary key of one table will be the Foreign key in another table. While inserting values into these tables, values in the primary key field must match the values in the foreign key field; otherwise, the foreign key column will not accept the INSERT query and throws an error.

The syntax to set a foreign key field in a table is −

CREATE TABLE table_name2(
   ... CONSTRAINT constraint_name
   FOREIGN KEY (column_name2)
   REFERENCES table_name1(column_name1)
);

Example

In the following example, we are creating a sample table named CUSTOMERS in the MySQL database −

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 let us insert some records into this table created using the INSERT statement as shown below −

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);

Verification

To verify the keys added to the CUSTOMERS table, let us display the table definition using the following query −

Currently, only PRIMARY KEY is set in the table on ID column. The NAME column acts like the Alternate Key, as it will only contain unique records like a Primary Key column. Whereas, both ID and NAME are the Candidate Keys in the CUSTOMERS table.

Example

To illustrate the usage of Foreign Key, we would need two tables. Following is the query to create another table ORDERS with the foreign key set as CUSTOMER_ID.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
   CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID)
);

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);

Verification

To verify if the ORDERS table is referenced to the CUSTOMERS table or not, we drop the CUSTOMERS table without dropping the ORDERS table.

DROP TABLE CUSTOMERS;

Following error is displayed −

ERROR 3730 (HY000): Cannot drop table ''customers'' referenced by a foreign key constraint ''fk_customers'' on table ''orders''.

Rules to be Followed for Alternate Keys

Below are list of rules of alternate keys that should be followed while inserting the record into a table −

• Alternate key values should be unique.
• Alternate key can not be NULL.

In MySQL, a Foreign Key is a column (or combination of columns) in a table whose values match the values of a Primary Key column in another table. Thus, using the Foreign key, we can link two tables together.

A Foreign Key is also known as a Referencing key of a table because it can reference any field defined as unique.

The table that has the primary key is known as the parent table and the key with the foreign key is known as the child table.

In addition to linking to tables, the Foreign Key constraint ensures referential integrity by preventing changes to data in the primary key table from invalidating the link to data in the foreign key table. i.e, a Foreign key prevents operations, like “dropping the table”, that would eliminate the connection between two tables.

Creating MySQL Foreign Key

We can create a Foreign Key on a MySQL table using the CONSTRAINT… FOREIGN KEY… REFERENCES keyword in the CREATE TABLE statement.

Syntax

Following is the syntax to add Foreign Key constraints on a column of a table −

CREATE TABLE table2(
   column1 datatype,
   column2 datatype,
   ...
   CONSTRAINT constraint_name
   FOREIGN KEY (column2)
   REFERENCES table1(column1)
);

Example

Let us create a table named CUSTOMERS using the CREATE TABLE statement −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) UNIQUE,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

To demonstrate the foreign key we need two tables so lets create another table as −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
   CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID)
);

Verification

To verify if the foreign key is created, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement −

DROP TABLE CUSTOMERS;

An error is displayed as follows −

ERROR 3730 (HY000): Cannot drop table ''customers'' referenced by a foreign key constraint ''fk_customers'' on table ''orders''.

Creating Foreign Key on Existing Column

We can also create a Foreign Key constraint on a column of an existing table using the ALTER TABLE… ADD CONSTRAINT statement.

Syntax

Following is the syntax to add foreign key constraint on an existing table −

ALTER TABLE table_name2
ADD CONSTRAINT constraint_name
FOREIGN KEY(column_name2)
REFERENCES table_name1(column_name1);

Example

Following is the MySQL query to add a foreign key constraint FK_CUSTOMERS on an existing column of an existing table ORDERS referencing primary key of CUSTOMERS table −

ALTER TABLE ORDERS
ADD CONSTRAINT FK_CUSTOMERS
FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMERS(ID);

Output

The table structure displayed will contain a FOREIGN KEY constraint on the CUSTOMER_ID column as shown −

Verification

To verify if the foreign key we created on ORDERS is referenced to CUSTOMERS table or not, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement −

DROP TABLE CUSTOMERS;

An error is displayed as follows −

ERROR 3730 (HY000): Cannot drop table ''customers'' referenced by a foreign key constraint ''fk_customers'' on table ''orders''.

Dropping MySQL Foreign Key

We can also drop the foreign key, created on a MySQL table, whenever it is no longer needed in that table. We can do this using the ALTER TABLE… DROP CONSTRAINT statement in MySQL.

Syntax

Following is the syntax to drop the foreign key from a table −

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

Using the following MySQL query, we are dropping the foreign key constraint from a table −

ALTER TABLE CUSTOMERS DROP CONSTRAINT fk_customers;

Verification

Let us verify whether the foreign key is dropped or not by dropping the CUSTOMERS table using the following query −

DROP TABLE CUSTOMERS;

Primary Key vs Foreign Key

Even though both the primary key and foreign key refer to the same column, there are many differences to be observed in the way they work. They are listed below.

Creating Foreign Key Using Client Program

We can also apply a Foreign Key constraint on a table field using a client program.

Syntax

$sql = ''CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30),
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))
$mysqli->query($sql);

sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
con.query(sql);

String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
statement.execute(sql);

foreign_key_query = ''CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))''
cursorObj.execute(foreign_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 customerss(Customer_Id INT, Customer_Name VARCHAR(30),
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))
if ($mysqli->query($sql)) {
    echo "foreign key column created successfully in customers table n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

foreign key 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);

  //creating a column that is foreign key!
  sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
    {
      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 ForeignKey {
   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 foreign key in the customer table...!;
         String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
         statement.execute(sql);
         System.out.println("Foreign key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customer");
         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);
      }
   }
}

Connected successfully...!
Foreign key created successfully...!
Customer_Id int YES MUL
Customer_Name varchar(30) YES

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Create table
foreign_key_query = ''''''CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))''''''
cursorObj.execute(foreign_key_query)
connection.commit()
print("Foreign key column is created successfully!")
cursorObj.close()
connection.close()

Foreign key column is created successfully!

A MySQL Composite Key is a key that consists of two or more columns in a table, used to uniquely identify a record (combination of values in the same table row). It can also be described as a Primary Key created on multiple columns.

With composite key on multiple columns of a table, a combination of these columns guarantees uniqueness, even though individually these columns may or may not guarantee uniqueness. Therefore, when the database table doesn”t have any column which is individually capable of identifying a unique row (or a record) from the table, then we might need two or more two fields/columns to get a unique record/row from the table.

Creating MySQL Composite Key

To create a composite key in a MySQL table, we create a primary key on two or more columns of a table using the PRIMARY KEY keyword in the CREATE TABLE statement. The composite key must have the following features −

• A Composite Key may or may not be a part of the Foreign key.
• A Composite Key can not be NULL.
• A Composite Key also can be created by combining more than one Candidate Key.
• It is also known as Compound key.
• All the attributes in a compound keys are foreign keys.

Syntax

Following is the syntax to create a Composite Key while creating a table −

CREATE TABLE table_name(
   column1 datatype, column2 datatype, column3 datatype...,
   CONSTRAINT composite_key_name
   PRIMARY KEY(column_name1, column_name2,..)
);

Example

In the following example, we are trying to create a table named CUSTOMERS and add a composite key on ID and NAME columns as shown −

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, NAME)
);

PRIMARY KEY is added to both ID and NAME columns in the CUSTOMERS table. The combination of values inserted into these columns must be unique, even if the individual column values has duplicates.

Verification

To verify if a composite key is created or not, let us display the table definition of a CUSTOMERS table using the DESC query −

Dropping MySQL Composite Key

We can drop the MySQL Composite Key by using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to drop the Composite key from the column of a table −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Using the following SQL statement, we can drop the Composite key constraint from the table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Verification

To verify if the Composite Key has been dropped or not, we display the CUSTOMERS table using the DESC keyword −

Composite Key Using a Client Program

We can also apply a Composite Key constraint on Fields to uniquely identified using a client program.

Syntax

$sql = ''ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)
$mysqli->query($sql);

sql = `CREATE TABLE employee(ID Int NOT NULL, emp_Id INT NOT NULL, emp_Name varchar(25), PRIMARY KEY(ID, emp_Id))`;
con.query(sql);

String sql = "Alter TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)";
statement.execute(sql);

composite_key_query = ''CREATE TABLE TEST(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, MOBILE BIGINT, CONSTRAINT CK_TEST PRIMARY KEY (ID, MOBILE))''
cursorObj.execute(composite_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.
'');

//creating composite key using alter statement.
$sql = ''ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)
if ($mysqli->query($sql)) {
    echo "composite key column created successfully in customers table n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

composite key 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);

  //creating a composite key column during the table creation...!
  sql = `CREATE TABLE employee(ID Int NOT NULL, emp_Id INT NOT NULL, emp_Name varchar(25), PRIMARY KEY(ID, emp_Id))`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE employee";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
    {
      id: 1,
      select_type: ''SIMPLE'',
      table: ''employee'',
      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 CompositeKey {
   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 composite key in the customers table...!;
         String sql = "Alter TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)";
         statement.execute(sql);
         System.out.println("Composite 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);
      }
   }
}

Connected successfully...!
Composite key created successfully...!
Cust_ID int NO PRI
Cust_Name varchar(30) NO PRI

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Create table
composite_key_query = ''''''CREATE TABLE TEST(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, MOBILE BIGINT,
CONSTRAINT CK_TEST PRIMARY KEY (ID, MOBILE))''''''
cursorObj.execute(composite_key_query)
connection.commit()
print("Composite key column is created successfully!")
cursorObj.close()
connection.close()

Composite key column is created successfully!