Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 21-04-2009
Member
 
Join Date: Feb 2009
Posts: 47
When to use foreign keys ?

hello,

What is foreign key in database systems ? and when to use foreign keys ?
Does anybody have an information about the same......please share your ideas
thank you
Reply With Quote
  #2  
Old 21-04-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: When to use foreign keys ?

In relational databases, a foreign key is a referential constraint between two tables.

Function - The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization.
Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.
Reply With Quote
  #3  
Old 21-04-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: When to use foreign keys ?

Defining foreign keys -

Code:
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.

Code:
CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER,
   ...
   CONSTRAINT col3_fk 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:

Code:
CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER REFERENCES other_table(column_name),
   ... )
Reply With Quote
  #4  
Old 21-04-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: When to use foreign keys ?

If you are using MySQL, then you can create foreign keys for Quicker Database Development,

Consider the following example Database

The example database is created as follows:

CREATE DATABASE mydb;
USE mydb;


We now define our two tables. Note that InnoDB is specified as the table type and we will also add an index for the employee’s last name.

CREATE TABLE employee (
id smallint(5) unsigned NOT NULL,
firstname varchar(30),
lastname varchar(30),
birthdate date,
PRIMARY KEY (id),
KEY idx_lastname (lastname)
) ENGINE=InnoDB;

CREATE TABLE borrowed (
ref int(10) unsigned NOT NULL auto_increment,
employeeid smallint(5) unsigned NOT NULL,
book varchar(50),
PRIMARY KEY (ref) ) ENGINE=InnoDB;


We can now specify our foreign key (this could be handled in the CREATE TABLE statement, but it is shown separately here):

ALTER TABLE borrowed
ADD CONSTRAINT FK_borrowed
FOREIGN KEY (employeeid) REFERENCES employee(id)
ON UPDATE CASCADE
ON DELETE CASCADE;


This tells MySQL that we want to alter the borrowed table by adding a constraint called ‘FK_borrowed’. The employeeid column will reference the id column in the employee table - in other words, an employee must exist before they can borrow a book.
Reply With Quote
  #5  
Old 21-04-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: When to use foreign keys ?

  • The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in as self-referencing or recursive foreign key.

  • 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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "When to use foreign keys ?"
Thread Thread Starter Forum Replies Last Post
Hardware Keys or Virtual Keys are best for Samsung Galaxy S III Nero Ferocious Portable Devices 5 09-05-2012 07:41 AM
Cursor keys bind as numpad keys in Alan Wake Mayan Video Games 1 22-02-2012 12:37 AM
Quick keys/volume control keys of Dell Wireless keyboard became inoperative Dimensioner Hardware Peripherals 3 07-03-2011 01:17 AM
Blocked Keys (Was EBAY Keys) Pomegranate Operating Systems 6 12-08-2010 04:07 PM
My Directional Keys, Shift+delete, End,Home keys Are Not Working Properly.. creativekrishna Hardware Peripherals 3 14-09-2009 10:00 AM


All times are GMT +5.5. The time now is 01:01 AM.