Primary Key And Foreign Key Relationship In SQL Server

Primary Key In SQL Server

• In SQL Server, a table typically has a primary key comprising a single column or combination of columns to uniquely identify each row within the table.
• In SQL Server, only one primary key constraint can be created per table.
• In SQL Server, the PRIMARY KEY constraint is used to create a primary key and enforce integrity of the entity of the table.
• In SQL Server, Two rows in a table cannot have the same primary key value and a column that is a primary key cannot have NULL value.

Foreign Key In SQL Server

• In SQL Server, a foreign key in a table is a column that points to a primary key column in another table.
• In SQL Server, Foreign key constraints are used to enforce referential integrity.

Creating 2 tables Customer And Order

Customer Table

In Above Customer Table “Customer Id” is a primary key column.

Order Table

In Above Order Table “Customer Id” is a foreign key column.

Important Points

• PRIMARY KEY CANNOT BE NULL OR DUPLICATE.
• FOREIGN KEY CAN BE NULL OR DUPLICATE.
• WE CANNOT INSERT IN ORDER TABLE WITH ID THAT IS NOT PRESENT IN CUSTOMER TABLE.
• WE CANNOT UPDATE IN ORDER TABLE WITH ID THAT IS NOT PRESENT IN CUSTOMER TABLE.
• WE CANNOT DELETE FROM CUSTOMER TABLE IF ID IS PRESENT IN ORDERS TABLE.

STEPS TO COVER:

• SYNTAX OF CREATING FOREIGN KEY WHILE CREATING A TABLE.
  • C_ID DATA_TYPE FOREIGN KEY REFERENCES CUSTOMER(C_ID)
• DROPPING FOREIGN KEY CONSTRAINT WITH ALTER STATEMENT.
  • SYNTAX: ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME.
• CREATING FOREIGN KEY IN EXISTING TABLE WITH ALTER STATEMENT.
  • SYNTAX: ALTER TABLE TABLE_NAME ADD FOREIGN KEY (C_ID) REFERENCES CUSTOMER(C_ID).

SQL Queries For Creating Customer And Order Table

create table Customer_tbl 
(
C_id int primary key, 
C_Name varchar(50), 
C_Address varchar(max), 
City varchar(50)
);

select * from Customer_tbl;

insert into Customer_tbl values(1,'Ali','Latifabad No: 8','Hyderabad');
insert into Customer_tbl values(2,'Anas','Latifabad No: 3','Hyderabad');
insert into Customer_tbl values(3,'Zain','Latifabad No: 6','Hyderabad');
insert into Customer_tbl values(4,'Amir','Latifabad No: 7','Hyderabad');

delete from Customer_tbl where C_id = 2;

create table [order] 
(
Ord_Id int primary key, 
Item varchar(50), 
Quantity int, 
Price_Of_1 int, 
C_id int foreign key references Customer_tbl(C_id)
);

select * from Customer_tbl;
select * from [order];

insert into [order] values(111,'Hardisk', 2, 500,3);
insert into [order] values(222,'Ram', 1, 300,1);
insert into [order] values(333,'Keyboard', 3, 400,4);
insert into [order] values(444,'Mouse', 2,300,2);
insert into [order] values(555,'Speaker', 1,3000,2);
insert into [order] values(666,'USB', 2,1000,5);

delete from [order] where ord_id = 666;

alter table [order] drop constraint [FK__order__C_id__05D8E0BE];
alter table [order] add foreign key (C_id) references Customer_tbl(C_id);

Click Below Link to Download Notes & SQL Queries Of This Blog

https://www.mediafire.com/file/eidde3b2qqrtmfc/PRIMARY+KEY+AND+FOREIGN+KEY+IN+SQL.rar/file