SQL Server Primary Key , Foreign Key Constraints in the table

The SQL Server Table might contain multiple fields but for the data Retrieval we need to consider an UNIQUE key through which the complete record row(s) can be fetched. The SQL Server RDBMS provides the concept of Primary Key (Unique field). It also helps in executing the queries faster.

Refer SQL Server Unique and Not Null Constraints in the previous blog.

http://oracleappshelp.com/2020/04/22/sql-server-unique-not-null-constraint-in-table/

SQL Server Primary Key – Points to Consider

  1. Primary Key should have UNIQUE field data
  2. Primary Key CANNOT be NULL
  3. Each Table Contains one Primary Key
  4. Primary Key could be based on single column or combination of multiple columns.

The below example shows creation of UserId field as PRIMARY KEY which includes single column.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
)

The below example shows creation of PRIMARY KEY which includes column combination of UserId + GivenName

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL ,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
	CONSTRAINT PK_Users PRIMARY KEY (UserId,GivenName)
);

SQL Server Drop Constraint

The SQL Server Primary Key Constraint can be removed using the below SQL Query

ALTER TABLE oracleappshelpUsers
DROP INDEX PK_Users; 

SQL Server Foreign Key – Points to Consider

Foreign Key concept is related to the Reference of another table where the reference column is the PRIMARY KEY.

Foreign Key – Points to Consider

  1. Foreign Key should be the Primary Key of the another table
  2. The table which actually contains the Foreing Key column as Primary Key is the Parent Table
  3. Foreign Key applies the linkage between two tables
  4. The table which has reference of Column as Foreign Key is the Child Table or Reference Table

Let’s discuss in detail

We cosnsider another table for storing the students data which also includes the field “UserId” from the table – oracleappshelpUsers

CREATE TABLE oracleappshelpStudents
(
    StudentId 		INT 					IDENTITY PRIMARY KEY,
    UserId 			INT 					FOREIGN KEY REFERENCES oracleappshelpUsers (UserId),
    Email 			VARCHAR(256) ,
	CreatedBy 			VARCHAR(128) 		NOT NULL,
	UpdatedBy 			VARCHAR(128) 		NOT NULL,
	CreatedDate			DATETIME			NOT NULL,
	UpdatedDate			DATETIME			NOT NULL
)

Here in this example UserId is the PRIMARY KEY for the table oracleappshelpUsers but it is being referenced in the New table oracleappshelpStudents and thus becomes the FOREIGN KEY.

SQL Server Alter table Add Foreign Key Constraint

The Foreign Key can be added to the existing table by using the SQL Server ALTER Table command.

ALTER TABLE oracleappshelpStudents
ADD CONSTRAINT FK_UserId
FOREIGN KEY (UserId) REFERENCES oracleappshelpUsers (UserId); 

SQL Server Drop Foreign Key Constraint

The below SQL Server Query can be used to Drop the Foreign Key Constraint

ALTER TABLE oracleappshelpStudents
DROP FOREIGN KEY FK_UserId; 
Bookmark and Share