SQL Foreign Key, SQL Tutorial, Global Guide Line Technology.
Bookmark Bookmark: SQL Foreign Key, SQL Tutorial Forum | Directory | Blog |

      Home                    

Basic SQL Guide.           
SQL Introduction.               
 What is Table?                  
 SQL Create Table.            
 SQL Insert Statement.      
 SQL Select Keyword.        
 SQL Order by clause.        
 SQL Count Statement.      
 SQL Group By Clause.      
 SQL Having Clause.          
 SQL Alias Tables.             
 SQL Join Tables.              
 SQL Outer Join.               
 SQL Update Statement.   
 SQL Delete Statement.    
 SQL Distinct Keyword.      
 SQL Where Keyword.       
 SQL And Or Keyword.      
 SQL In Keyword.              
 SQL Between Keyword.   
 SQL Like Keyword.          
 SQL Functions.                
 SQL Concatenate.           
 SQL Substrings.               
 SQL Trim Function.         
 SQL Constraints.              
 SQL Primary Key.            
 SQL Foreign Key.
 SQL Cerate Views.           
 SQL Create Index.           
 SQL Alter Table.             
 SQL Drop Table.             
 SQL Truncate Table.      
 SQL Summary.                


Submit your link

SQL Tutorial >> SQL Foreign Key.


     Back             Next     



Now SQL Tutorial will discus SQL Foreign Key Constraint, Foreign Key Constraint is a field or fields that points to the SQL Primary Key Constraint of another table. The purpose of the SQL Foreign Key Constraint is to ensure referential integrity of the data. In other words, only those values that are supposed to appear in the database are permitted.

For example, we have two tables, a Employees table that includes all employees data, and an Employees_Salary table that includes all employees salary records. The constraint here is that all employees salaries must be associated with an employee that is already in the Employees table. In this case, we will place an SQL Foreign Key Constraint on the Employees_Salary table and have it relate to the SQL Primary Key Constraint of the Employees table. Now, we can ensure that all employees salary in the Employees_Salary table are related to a employees in the Employees table. In other words, the Employees_Salary table cannot contain information of any employee that is not in the Employees table.

Examples of SQL Foreign Key Constraints

The structure of these two tables will be as follows to explain the SQL Foreign Key Constraint

Structure of Employees

empID Integer, Not Null and Primary Key
FirstName Varchar(50)
LastName Varchar(50)
Email Varchar(50)
Phone Integer


Structure of Employees_Salary

salID Integer, Not Null
Month Date and Time
Salary Integer
empID Foreign Key

In the above structure example, the empID column in the Employees_Salary table is a foreign key pointing to the empID column in the Employees table we can also change its name from empID to some others one but this convention is not recommended.  And if we use different names then the database will not generate error or did not disallow us.  But for our own acknowledgment and for our own convenience we always use the same names for the Primary key column and Foreign Key columns.

Below we show examples of how to specify the foreign key when creating the Employees_Salary table:

Primary And Foreign Key Constraint With MySQL Create Table.

CREATE TABLE Employees_Salary
(salID integer,
Month date,
Salary integer,
empID integer,
Primary Key (salID),
Foreign Key (empID) references Employees(empID));

Foreign Key Constraint With Oracle Create Table.

CREATE TABLE Employees_Salary
(salID integer primary key,
Month date,
Salary integer,
empID integer references Employees(empID)) ;

Foreign Key Constraint With SQL Server Create Table.

CREATE TABLE Employees_Salary
(salID integer primary key,
Month datetime,
Salary integer,
empID integer references Employees(empID)) ;



Below are examples for specifying an SQL Foreign Key Constraint by SQL Altering Table command. Now assume that the Employees_Salary table has been created, and the foreign key has not yet been put in.

Foreign Key Constraints With MySQL Alter Table.

ALTER TABLE Employees_Salary
ADD FOREIGN KEY (empID) REFERENCES Employees(empID);

Foreign Key Constraints With Oracle Alter Table.

ALTER TABLE Employees_Salary
ADD (CONSTRAINTS fk_Foreign_Key_Name) FOREIGN KEY (empID) REFERENCES Employees(empID);

Foreign Key Constraints With SQL Server Alter Table.

ALTER TABLE Employees_Salary
ADD FOREIGN KEY (empID) REFERENCES Employees(empID);


     Back             Next     


 

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]

Copyright 2005 -  2014 www.globalguideline.com All rights reserved. (Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher) 290 visitors are online now