Now, we will add a Foreign Key Constraint on the ID column of the ORDERS table.įollowing is the SQL query to add the foreign key constraint on an the column of an existing table −įollowing is the output of the above program − ExampleĪssume the CUSTOMERS and ORDERS tables have already been created in the SQL database. It is optional to specify the name of a constraint but it comes in handy while dropping the constraint. Here, FK_ORDERS is the name of the foreign key constraint. Using the ALTER TABLE statement we can add a Foreign Key constraint on an existing column in a table in MySQL database as shown below − This is useful when you forget to add a Foreign Key constraint on a column while creating a table, or when you want to add this constraint on another column even if one Foreign Key column exists in a table. We can also create a Foreign key constraint on a column of an existing table. If you verify the error message below, you will observe that it says that the table can not be dropped because it is referenced by a FOREIGN KEY constraint −ĮRROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'FK_CUSTOMER' on table 'orders'.įoreign Key Constraint on an Existing Column We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column named ID of the CUSTOMERS table so you can't drop table1 (CUSTOMERS) before dropping the table2 (ORDERS).įirst of all, let's drop the CUSTOMERS table without dropping the ORDERS table using the DROP TABLE statement − The above statement produces the following output − While doing so, we add the foreign key constraint on column CUSTOMER_ID reference on column ID of the CUSTOMERS table as shown in the statement below − The following query creates a table with the name CUSTOMERS −įollowing is the output of the above SQL statement − Let us create two tables with the names CUSTOMERS and ORDERS. REFERENCES referenced_table(referenced_column) It helps to normalize (or organize the data in a database) the data in multiple tables.įollowing is the basic syntax to add Foreign Key constraints on a column of a table in MySQL database − Here the id of the customer is primary key (ID) in the CUSTOMERS table and foreign key in the ORDERS (CUSTOMER_ID) table observe the following diagram − Features of Foreign Keyįollowing is the of features of Foreign Key −Ī Foreign Key is used to reduce the redundancy (or duplicates) in the table. Let's consider an example scenario, assume we have two tables namely CUSTOMERS (ID, NAME, AGE, ADDRES, SALARY) and ORDERS (ID, DATE, CUSTOMER_ID, AMOUNT). 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. The foreign key can reference the unique fields of any table in the database. In SQL, a Foreign Key is a column in one table that matches a Primary Key in another table, allowing the two tables to be connected together.Ī foreign key also maintains referential integrity between two tables, making it impossible to drop the table containing the primary key (preserving the connection between the tables).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |