Difference Between Primary Key and Foreign Key

Google Advertisements

SQL Normalization SQL DDL Command

Difference Between Primary Key and Foreign Key

primary key and foreign key which seems identical, but actually both are different in features and behaviours.

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. In other words, every value is unique for Primary Key.

Syntax Primary Key

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Foreign key in a Table

A Foreign key is a column in one table which is the primary key on another table. Foreign key and a Primary key is used to define relationship between two tables in relational database. For example in Employee and Department relationship, we have two tables Department(dept_id, dept_name) and Employee (emp_id, emp_name, dept_id). dept_id is primary key in Department table and foreign key in Employee table.

Difference Between Primary Key and Foreign Key

Primary KeyForeign Key
Primary key uniquely identify a record in the table.Foreign key is a field in the table that is primary key in another table.
Primary Key can't accept null values.Foreign key can accept multiple null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
We can have only one Primary key in a table.We can have more than one foreign key in a table.
The primary key of a particular table is the attribute which uniquely identifies every record and does not contain any null value.The foreign key of a particular table is simply the primary key of some other table which is used as a reference key in the second table.
A primary key attribute in a table can never contain a null value.A foreign key attribute may have null values as well.
Not more than one primary key is permitted in a table.A table can have one or more than one foreign key for referential purposes.
Duplicity is strictly prohibited in the primary key; there cannot be any duplicate values.Duplicity is permitted in the foreign key attribute, hence duplicate values are permitted.

Defining Primary key and Foreign key

Syntax

 --Create Parent Table 
CREATE TABLE Department 
 (
 DeptID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 Address varchar(100) NULL
)
 GO 
 --Create Child Table
CREATE TABLE Employee
 (
 EmpID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 Salary int NULL,
 --define foreign key
 DeptID int FOREIGN KEY REFERENCES Department(DeptID)
 )
difference between primary key and foreign key in sql
SQL Normalization SQL DDL Command

Google Advertisements

We have more than 5 Groups which have 500000+ Members and some Pages have more than 100000+ members. For Advertisement contact on [email protected] or 8076671483

Buy This Ad Space @$50 per Month, Ad Size 600X200 Contact on: [email protected] or 8076671483

Pure VPN Privide Lowest Price VPN Just @ $1.65. Per Month with Non Detected IP Lowest Price Non Detected IP VPN

Classpath variable is set for providing a path for predefined Java classes which is used in our application. All classes are available in lib/rt.jar so we set classpath upto lib/rt.jar. Difference between path and classpath in Java

Cloud computing is the on demand availability of computer system resources, especially data storage and computing power, without direct active management by the user. Cloud Computing Tutorial

College Projects Related to Java, AWT, C Projects for College, C++ Projects for College, Android Projects. Download Java C C++ Projects

Download Projects

Adsense Advertisements


Buy This ads Space 8076671483

Buy This Ad Space @$120 per Month, Ad Size 300X600 Contact on: [email protected] or 8076671483 Try this Keyword C++ Programs