Jump to content

User:Nalvi2012/sandbox

From Wikipedia, the free encyclopedia

In a relational database, foreign key is a column field in a child table that matches the candidate key column field in a parent table. A candidate key can either be a primary or a unique key. Foreign key is used to link the two relational tables together, thus forming a referential constraint between the tables.

To illustrate this, assume that there is a PERSON table and an EMPLOYEE table. The employee is also a person and therefore shares the attributes in the PERSON table.

The PERSON table has the following column fields: id, social security number, last name, first name, address and telephone shown in the table below.

Id SSN (primary key of PERSON table) LastName FirstName Address Telephone
5 123-45-6789 Doe John 12 Data Street 555-1212
6 987-65-4321 Smith Mary Mary 53 Base Street 555-1234

The primary key of this table can be set as SSN so that all the information about a particular person (John or May) can be pulled out by entering their individual SSN.

The EMPLOYEE table has the following column fields: Id, social security number, start date and hourly rate shown in the table below.

Id (primary key of EMPLOYEE table) SSN (foreign key to PERSON table) StartDate HourlyRate
1 987-65-4321 8/12/2007 $50

The primary key of the table can be set as Id, but an additional foreign key, SSN, can also be set so the attributes of the person field can be retrieved through a query on the EMPLOYEE table and the EMPLOYEE table can be linked to the PERSON table without having to duplicate any of the attributes of the PERSON table.

The EMPLOYEE table tells us that the SSN of employee number 1 matches the SSN of Mary Smith, who lives at 53 Base Street and whose telephone number is 555-1234.

The PERSON and EMPLOYEE tables can be created in SQL by either a CREATE TABLE statement or ALTER TABLE statement. Primary key is defined in the PERSON table.

PERSON 

CREATE TABLE person (
  SSN int(11) NOT NULL,
  LastName varchar(20) NOT NULL,
  FirstName varchar(20) NOT NULL,
  Address varchar(20) DEFAULT NULL,
  Telephone varchar(12) DEFAULT NULL,
  PRIMARY KEY (SSN)
)

Primary and foreign keys are defined in the EMPLOYEE table.

EMPLOYEE 

CREATE TABLE employee (
  ID int(11) NOT NULL AUTO_INCREMENT,
  SSN int(11) DEFAULT NULL,
  StartDate date DEFAULT NULL,
  HourlyRate int(11) DEFAULT NULL,
  PRIMARY KEY (ID),
  KEY SSN (SSN),
  CONSTRAINT SSN FOREIGN KEY (SSN) REFERENCES person (SSN) 
	ON DELETE NO ACTION ON UPDATE CASCADE
)

It should be noted that, although SQL standard does require that the referenced attributes form a primary or a unique key, some database vendors impose the primary key restriction. SQL relaxes the foreign-key constraint by letting foreign keys have null values.[1]

Interestingly, a foreign key can reference an attribute in its own table. These are called self-referencing tables. For example, consider an EMPLOYEE table that contains three columns: employee_number, employee_name, and manager_employee_number. Because the manager is also an employee, there is a foreign key relationship from the manager_employee_number column to the employee_number column. [2]

Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (parent table or referenced table) to many (child table, or referencing table) relationship. A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys. Improper foreign key/primary key relationships or not enforcing those relationships are often the source of many database and data modeling problems.


Defining foreign keys

[edit]

Foreign keys are defined in the ANSI SQL Standard, through a FOREIGN KEY constraint. The syntax to add such a constraint to an existing table is defined in SQL:2003 as shown below. Omitting the column list in the REFERENCES clause implies that the foreign key shall reference the primary key of the referenced table. 

ALTER TABLE <table identifier>
   ADD [ CONSTRAINT <constraint identifier> ]
      FOREIGN KEY ( <column expression> {, <column expression>}... )
      REFERENCES <table identifier> [ ( <column expression> {, <column expression>}... ) ]
      [ ON UPDATE <referential action> ]
      [ ON DELETE <referential action> ]

Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement. 

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER,
   ...
   FOREIGN KEY(col3)
      REFERENCES other_table(key_col) ON DELETE CASCADE,
   ... )

If the foreign key is a single column only, the column can be marked as such using the following syntax: 

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER REFERENCES other_table(column_name),
   ... )

Foreign keys can be defined with a stored procedure statement.[further explanation needed] 

sp_foreignkey tabname, pktabname, col1 [, col2] ...  [, col8]
  • tabname: the name of the table or view that contains the foreign key to be defined.
  • pktabname: the name of the table or view that has the primary key to which the foreign key applies. The primary key must already be defined.
  • col1: the name of the first column that makes up the foreign key. The foreign key must have at least one column and can have a maximum of eight columns.

Referential actions

[edit]
Main article: Propagation constraint

Because the database management system enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. SQL:2003 specifies 5 different referential actions that shall take place in such occurrences:

CASCADE

[edit]

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[3]).

Example Tables: Customer(customer_id, cname, caddress) and Order(customer_id, products, payment)

Customer is the master table and Order is the child table, where 'customer_id' is the foreign key in Order and represents the customer who placed the order. When a row of Customer is deleted, any Order row matching the deleted Customer's customer_id will also be deleted.

NOTE: In Microsoft SQL, a cascading delete to a self-referencing table is not allowed. You must either use a trigger, create a stored procedure, or handle the cascading delete from the calling application. An example of this is where a single table has an ID as identity and a ParentID with a relationship to ID in the same table.

RESTRICT

[edit]

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table.

Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

[edit]

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. This is different from RESTRICT, which assumes at the outset that the operation will violate the constraint. Using NO ACTION, the triggers or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.

SET DEFAULT

[edit]

Similar to SET NULL[clarify], the foreign key values in the referencing row are set to the column default[clarify] when the referenced row is updated or deleted.

Triggers

[edit]

Referential actions are generally implemented as implied triggers (i.e. triggers with system-generated names, often hidden.) As such, they are subject to the same limitations as user-defined triggers, and their order of execution relative to other triggers may need to be considered; in some cases it may become necessary to replace the referential action with its equivalent user-defined trigger to ensure proper execution order, or to work around mutating-table limitations.

Another important limitation appears with transaction isolation: your changes to a row may not be able to fully cascade because the row is referenced by data your transaction cannot "see", and therefore cannot cascade onto. An example: while your transaction is attempting to renumber a customer account, a simultaneous transaction is attempting to create a new invoice for that same customer; while a CASCADE rule may fix all the invoice rows your transaction can see to keep them consistent with the renumbered customer row, it won't reach into another transaction to fix the data there; because the database cannot guarantee consistent data when the two transactions commit, one of them will be forced to roll back (often on a first-come-first-served basis.)

Example

[edit]

As a first example to illustrate foreign keys, suppose an accounts database has a table with invoices and each invoice is associated with a particular supplier. Supplier details (such as name and address) are kept in a separate table; each supplier is given a 'supplier number' to identify it. Each invoice record has an attribute containing the supplier number for that invoice. Then, the 'supplier number' is the primary key in the Supplier table. The foreign key in the Invoices table points to that primary key. The relational schema is the following. Primary keys are marked in bold, and foreign keys are marked in italics. 

  Supplier ( SupplierNumber, Name, Address, Type )
  Invoices ( InvoiceNumber, SupplierNumber, Text )

The corresponding Data Definition Language statement is as follows. 

  CREATE TABLE Supplier (
     SupplierNumber  INTEGER NOT NULL,
     Name            VARCHAR(20) NOT NULL,
     Address         VARCHAR(50) NOT NULL,
     Type            VARCHAR(10),
     CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
     CONSTRAINT number_value CHECK (SupplierNumber > 0) )

  CREATE TABLE Invoices (
     InvoiceNumber   INTEGER NOT NULL,
     SupplierNumber  INTEGER NOT NULL,
     Text            VARCHAR(4096),
     CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
     CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
     CONSTRAINT supplier_fk FOREIGN KEY(SupplierNumber)
        REFERENCES Supplier(SupplierNumber)
        ON UPDATE CASCADE ON DELETE RESTRICT )

See also

[edit]

References

[edit]
  1. ^ Kifer, Michael; Bernstein, Arthur; Lewis, Philip M. (2005). "3". In Sullivan, Susan Hartman (ed.). Database Systems: An Application Oriented Approach (2nd ed.). Addison-Wesley. ISBN 0-321-22838-3.
  2. ^ "FOREIGN KEY Constraints". Retrieved 2012-10-04.
  3. ^ http://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade
[edit]


Retrieved from "https://en.wikipedia.org/w/index.php?title=User:Nalvi2012/sandbox&oldid=1086838405"