So if we have particular data(Say id 1003) in the parent table, then it is not necessary to have that data in the child table, but vice versa is not true. Let’s suppose these tables have the following values.įor cust_id 1001 there are three orders that exist in the orders table. In the above example, the Cust_id column in the ORDERS table is a foreign key pointing to the Cust_id column in the CUSTOMERS table. The Customers table is having all the customer-related data, and the Orders table having customer order-related data. Say we have two tables, Customers and Orders. However, if the update rule is NO ACTION, parent unique keys can be updated as long as every child has a parent key by the time the update statement completes. The value of the parent’s unique keys cannot be changed if the update rule is RESTRICT and there are one or more dependent rows.When we insert other than the NULL value into the foreign key constraint, the value must exist in the referenced column else, a violation message occurred.We can also insert NULL values in the child table.Foreign key constraints can refer to the tables within the same database.FOREIGN KEY constraints can reference another column in the same table.Foreign key constraint does not have to be linked only to the primary key of another table it can also be linked to the UNIQUE constraint of another table.Foreign keys are not applicable to temporary tables.After deleting or updating parent data, The child data will either be deleted or updated.Ĭreating a foreign key in an existing table requires ALTER permission on the table. CASCADE- We can use this together with ON DELETE and ON UPDATE.SET NULL, NO ACTION, CASCADE, SET DEFAULT are some of the values of this parameter. ON UPDATE- This is an optional parameter that takes action on the child data after an update on the parent data.ON DELETE- This parameter takes action on child data after the deletion of parent data.The primary key of parent_table is referenced in child_table Parent_Table- This is the name of parent_table.child_column1, child_column2…child_column_n- It is the name of child_Table columns to reference the primary key in the parent table.F_key- This is a foreign key constraint.column_1, column_2- the columns to be added to the table.Child_Table is the table name which we will create.REFERENCES parent_Table (parent_column1, parent_column2. Hadoop, Data Science, Statistics & othersĬreating a new table with a foreign key requires CREATE TABLE permission in the database.įOREIGN KEY (child_column1, child_column2.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |