Alter order dan foreign key mysql

Foreign Key helps establish database relationships and maintain referential integrity. They help link one or more columns in one table to another table. Here’s how to add foreign key in MySQL.

How to Add Foreign Key in MySQL

Here are the steps to add foreign key in MySQL. You can add foreign key constraint using CREATE TABLE or ALTER TABLE statements in SQL.

Here’s the syntax to create foreign key in MySQL.

Using ALTER TABLE

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (foreign_key_name,...) 
REFERENCES parent_table(column_name,...);

In the above query, table_name is the the table where you want to add foreign key. constraint_name is the name of the foreign key constraint. foreign_key_name, … is the list of foreign key columns.

parent_table is the table to which your foreign_key references, followed by list of column names in that table

Bonus Read : MySQL Alter Table Column

Using CREATE TABLE

CREATE TABLE table_name(
    column_name column_description,
    CONSTRAINT constraint_name
    FOREIGN KEY (foreign_key_name,...) 
        REFERENCES parent_table(column_name,...)
)

In the above query, table_name is the table where you want to add foreign key. constraint_name is the name of the foreign key constraint. foreign_key_name, … is the list of foreign key columns.

parent_table is the table to which your foreign_key references, followed by list of column names in that table.

Please note, in ALTER TABLE you need to use ADD CONSTRAINT while in CREATE TABLE you need to use only CONSTRAINT keyword.

Bonus Read : MySQL DROP FOREIGN KEY Constraint

MySQL ADD FOREIGN KEY Examples

Let’s say you have the following tables.

Let’s create 2 tables (categories and orders) and add foreign key constraint to orders, referencing id column in categories table.

mysql> create table categories(id int auto_increment primary key,name varchar(255));

mysql> create table orders(id int auto_increment primary key,category_id int,
 CONSTRAINT fk_cat 
 FOREIGN KEY (category_id) 
 REFERENCES categories(id));

mysql> describe orders;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| category_id | int(11) | YES  | MUL | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Bonus Read : MySQL DROP UNIQUE CONSTRAINT

Let’s take a look at the same example using ALTER TABLE statement.

mysql> create table orders(id int auto_increment primary key,category_id int);

mysql> create table categories(id int auto_increment primary key,name varchar(255));

mysql> ALTER TABLE orders
     ADD CONSTRAINT fk_cat
     FOREIGN KEY (category_id)
     REFERENCES categories(id);

mysql> describe orders;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| category_id | int(11) | YES  | MUL | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Related posts:

• About Author

The foreign key is used to link one or more than one table together. It is also known as the referencing key. A foreign key matches the primary key field of another table. It means a foreign key field in one table refers to the primary key field of the other table. It identifies each row of another table uniquely that maintains the referential integrity in MySQL.

A foreign key makes it possible to create a parent-child relationship with the tables. In this relationship, the parent table holds the initial column values, and column values of child table reference the parent column values. MySQL allows us to define a foreign key constraint on the child table.

MySQL defines the foreign key in two ways:

1. Using CREATE TABLE Statement
2. Using ALTER TABLE Statement

Syntax

Following are the basic syntax used for defining a foreign key using CREATE TABLE OR ALTER TABLE statement in the MySQL:

In the above syntax, we can see the following parameters:

constraint_name: It specifies the name of the foreign key constraint. If we have not provided the constraint name, MySQL generates its name automatically.

col_name: It is the names of the column that we are going to make foreign key.

parent_tbl_name: It specifies the name of a parent table followed by column names that reference the foreign key columns.

Refrence_option: It is used to ensure how foreign key maintains referential integrity using ON DELETE and ON UPDATE clause between parent and child table.

MySQL contains five different referential options, which are given below:

CASCADE: It is used when we delete or update any row from the parent table, the values of the matching rows in the child table will be deleted or updated automatically.

SET NULL: It is used when we delete or update any row from the parent table, the values of the foreign key columns in the child table are set to NULL.

RESTRICT: It is used when we delete or update any row from the parent table that has a matching row in the reference(child) table, MySQL does not allow to delete or update rows in the parent table.

NO ACTION: It is similar to RESTRICT. But it has one difference that it checks referential integrity after trying to modify the table.

SET DEFAULT: The MySQL parser recognizes this action. However, the InnoDB and NDB tables both rejected this action.

NOTE: MySQL mainly provides full support to CASCADE, RESTRICT, and SET NULL actions. If we have not specified the ON DELETE and ON UPDATE clause, MySQL takes default action RESTRICT.

Foreign Key Example

Let us understand how foreign key works in MySQL. So first, we are going to create a database named "mysqltestdb" and start using it with the command below:

Next, we need to create two tables named "customer" and "contact" using the below statement:

Table: customer

Table: contact

Table Structure Verification

Here, we are going to see how our database structure looks like using the following queries:

We will get the structure as below:

In the above output, we can see that the PRI in the key column of the customer table tells that this field is the primary index value. Next, the MUL in the key column of the contact value tells that the Customer_Id field can store multiple rows with the same value.

Insert Data to the Table

Now, we have to insert the records into both tables. Execute this statement to insert data into table customer:

After insertion, execute the SELECT TABLE command to check the customer table data as below:

Execute the below insert statement to add data into a table contact:

Our contact table looks like as below:

Now, let's see how foreign keys in MySQL preserve data integrity.

So here, we are going to delete the referential data that removes records from both tables. We have defined the foreign key in the contact table as:

It means if we delete any customer record from the customer table, then the related records in the contact table should also be deleted. And the ON UPDATE CASCADE will updates automatically on the parent table to referenced fields in the child table(Here, it is Customer_Id).

Execute this statement that deletes a record from the table whose name is JOHN.

Again, if we look at our tables, we can see that both tables were changed. It means the fields with name JOHN will be removed entirely from both tables.

Now, test the ON UPDATE CASCADE. Here, we are going to update the Customer_Id of Mary in the contact table as:

Again, if we look at our tables, we can see that both tables were changed with Customer_Id of Mary=3.

Foreign Key example using SET NULL action

Here, we are going to understand how the SET NULL action works with a foreign key. First, we have to create two table named Persons and Contacts, as shown below:

Table: Persons

Table: Customers

Next, we need to insert the data into both tables using the following statement:

Now, update the ID of the "Persons" table:

Finally, verify the update using the SELECT statement given below:

If we look at our tables, we can see that both tables were changed. The rows with a Person_Id=3 in the Contacts table automatically set to NULL due to the ON UPDATE SET NULL action.

How to DROP Foreign Key

MySQL allows the ALTER TABLE statement to remove an existing foreign key from the table. The following syntax is used to drop a foreign key:

Here, the table_name is the name of a table from where we are going to remove the foreign key. The constraint_name is the name of the foreign key that was added during the creation of a table.

If we have not known the name of an existing foreign key into the table, execute the following command:

It will give the output as below where we can see that the table contact has one foreign key named fk_customer shown in the red rectangle.

Now, to delete this foreign key constraint from the contact table, execute the statement as below:

We can verify whether foreign key constraint removes or not, use the SHOW CREATE TABLE statement. It will give the output as below where we can see that the foreign key is no longer available in the table contact.

Define Foreign Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table. Sometimes there is a need to add a foreign key to the column of an existing table; then, this statement is used to add the foreign key for that column.

Syntax

Following are the syntax of the ALTER TABLE statement to add a foreign key in the existing table:

When we add a foreign key using the ALTER TABLE statement, it is recommended to first create an index on the column(s), which is referenced by the foreign key.

Example

The following statement creates two tables, "Person" and "Contact", without having a foreign key column into the table definition.

Table: Person

Table: Contact

After creating a table, if we want to add a foreign key to an existing table, we need to execute the ALTER TABLE statement as below:

Foreign Key Checks

MySQL has a special variable foreign_key_cheks to control the foreign key checking into the tables. By default, it is enabled to enforce the referential integrity during the normal operation on the tables. This variable is dynamic in nature so that it supports global and session scopes both.

Sometimes there is a need for disabling the foreign key checking, which is very useful when:

• We drop a table that is a reference by the foreign key.
• We import data from a CSV file into a table. It speeds up the import operation.
• We use ALTER TABLE statement on that table which has a foreign key.
• We can execute load data operation into a table in any order to avoid foreign key checking.

The following statement allows us to disable foreign key checks:

The following statement allows us to enable foreign key checks:

How can I change foreign key in MySQL?

How do you modify a foreign key constraint?

Which field is a foreign key in order table?

What is set Foreign_key_checks 0?