PRIMARY KEY FOREIGN KEY IN SQL
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

CUSTOMER IDCUSTOMER NAMEADDRESSCITY
1AliLatifabad Unit: 12Hyderabad
2AmirLatifabad Unit: 11Hyderabad
3AnasGulshan e IqbalKarachi
4UsmanLatifabad Unit: 8Hyderabad
5AfnanLatifabad Unit: 6Hyderabad

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

Order Table

ORDER IDORDER ITEMORDER QUANTITYPRICE OF 1CUSTOMER ID
100Hardisk25004
101Ram13502
102Printer240003
103Monitor315004
104Mouse33005

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

No responses yet

Leave a Reply

Your email address will not be published. Required fields are marked *