A foreign key is a column or set of columns in one table that corresponds in exact order to a column or set of columns defined as a PRIMARY KEY or a UNIQUE constraint in another table. In its simplest form, it implements an optional one-to-many relationship.
The standard entity-relationship model depicts a simple one-to-many relationship between two entities as shown in Figure.
If we implement this model as two tables, PARENT and CHILD, then rows in the CHILD table are dependent on the existence of a linking row in PARENT. Firebird’s FOREIGN KEY (FK) constraint enforces this relationship in the following ways:
Implementing the Constraint
In order to implement the referential constraint, certain prerequisites must be attended to. In this section, we follow through a very simple example. If you are developing in an existing, complex environment, where SQL privileges are in effect, then you may need to be concerned about the REFERENCE privilege. It is introduced in a separate section later in this chapter.
The Parent Structure
It is necessary to start with the parent table and implement a controlling unique key to which the dependent table will link. This is commonly the primary key of the parent table, although it need not be. A foreign key can link to a column or group that has been constrained using the UNIQUE constraint. For present purposes, we will use the primary key:
The Child Structure
In the child structure, we need to include a column, PARENT_ID, that exactly matches the primary key of the parent in type and size (and also column order, if the linkage involves multiple columns):
The next thing to do is declare the relationship between the child and the parent by means of a FOREIGN KEY constraint.
Syntax for Defining a FOREIGN KEY
The syntax pattern for a referential integrity definition is as follows:
Defining our foreign key:
Firebird stores the constraint FK_CHILD_PARENT and creates an ordinary index on the column(s) named in the FOREIGN KEY argument. In Firebird 1.5, the index will be named FK _CHILD _PARENT as well, unless you used the optional USING clause to assign a different name to the index. In Firebird 1.0.x , the index name will be INTEG_nn
(where nn is a number).
The two tables are now engaged in a formal referential integrity constraint. We can add new rows to PARENT without restriction:
However, there are restrictions on CHILD. We can do this:
Because the nullable column PARENT_ID was omitted from the column list, NULL is stored there. This is allowed under the default integrity rules. The row is an orphan.
However, we get a constraint error if we try to do this:
violation of FOREIGN KEY constraint "FK_CHILD_PARENT" on table "CHILD"
There is no row in PARENT having a PK value of 2, so the constraint disallows the insert.
Both of the following are allowed:
Now, the PARENT row with ID=1 has two child rows. This is the classic master-detail structure —an uncomplicated implementation of the one-to-many relationship. To protect the integrity of the relationship, the default rules will disallow this:DELETE FROM PARENT WHERE ID = 1;
Action Triggers to Vary Integrity Rules
Obviously, integrity rules take effect whenever some change in data occurs that affects the relationship. However, the default rules do not suit every requirement. We may want to override the rule that permits child rows to be created as orphans or to be made orphans by having their foreign key set to null in an operation. If it is a problem for our business rules that a parent row cannot be deleted if it has dependent child rows, we may want Firebird to take care of the problem automatically. Firebird’s SQL language can oblige, through its optional automatic action triggers:
Automatic Action Triggers
Firebird provides the optional standard DML events ON UPDATE and ON DELETE, along with a range of action options to vary the referential integrity rules. Together, the DML event and the automatic behavior specified by the action option form the action trigger —the action to be taken in this dependent table when updating or deleting the referenced key in the parent. The actions defined include cascading the change to associated foreign table(s).
Action Trigger Semantics
Because this is the default action trigger, the keyword can be—and usually is—omitted. The DML operation on the parent’s PK leaves the foreign key unchanged and potentially causes the operation on the parent to fail.
ON UPDATE CASCADE
In the dependent table, the foreign key corresponding to the old value of the primary key is updated to the new value of the primary key.
ON DELETE CASCADE
In the dependent table, the row with the corresponding key is deleted.
The foreign key corresponding to the old parent PK is set to NULL—the dependent rows become orphans. Clearly, this action trigger cannot be applied if the foreign key column is non-nullable.
The foreign key corresponding to the old parent PK is set to its default value. There are some “gotchas” about this action that are important to know about:
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.