Define Foreign Key In Different Ways – SQL Server

A foreign key (FK) is a column or group of columns used to create and enforce a link between data in two tables in order to control the data that can be put in the foreign key table. When the column or columns that carry the primary key value for one table are referenced by the column or columns in another table, a link is formed between the two tables. In the second table, this column becomes a foreign key.

The FK can be defined in the following ways, each of which appears distinct but is the same in the end. The only difference is that you can specify a name for the constraint or let the system generate one. If you need to delete something, a user-defined name is preferable.

Example-1:

CREATE TABLE tbStudentDetails(
iStudentID INT PRIMARY KEY,
vcStudentName VARCHAR(100),
iPhoneNumber BIGINT,
vcEmail VARCHAR(100));
 
CREATE TABLE tbStudentPayments(
PaymentId INT PRIMARY KEY,
iStudentID INT REFERENCES tbStudentDetails(iStudentID),
dcPayment DECIMAL(10,2),
dtPaymentDate DATETIME,
PaymentStatus VARCHAR(15));

Example-2:

CREATE TABLE tbStudentDetails(
iStudentID INT PRIMARY KEY,
vcStudentName VARCHAR(100),
iPhoneNumber BIGINT,
vcEmail VARCHAR(100));
 
CREATE TABLE tbStudentPayments(
PaymentId INT PRIMARY KEY,
iStudentID INT FOREIGN KEY REFERENCES tbStudentDetails(iStudentID),
dcPayment DECIMAL(10,2),
dtPaymentDate DATETIME,
PaymentStatus VARCHAR(15));

Example-3:

CREATE TABLE tbStudentDetails(
iStudentID INT PRIMARY KEY,
vcStudentName VARCHAR(100),
iPhoneNumber BIGINT,
vcEmail VARCHAR(100));
 
CREATE TABLE tbStudentPayments(
PaymentId INT PRIMARY KEY,
iStudentID INT,
dcPayment DECIMAL(10,2),
dtPaymentDate DATETIME,
PaymentStatus VARCHAR(15),
CONSTRAINT FK_StudentID FOREIGN KEY (iStudentID)
REFERENCES tbStudentDetails(iStudentID) );

a

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s