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
- Primary Key should have UNIQUE field data
- Primary Key CANNOT be NULL
- Each Table Contains one Primary Key
- 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
- Foreign Key should be the Primary Key of the another table
- The table which actually contains the Foreign Key column as Primary Key is the Parent Table
- Foreign Key applies the linkage between two tables
- 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;