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.

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 Foreign 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 consider 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;