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 ID | CUSTOMER NAME | ADDRESS | CITY |
1 | Ali | Latifabad Unit: 12 | Hyderabad |
2 | Amir | Latifabad Unit: 11 | Hyderabad |
3 | Anas | Gulshan e Iqbal | Karachi |
4 | Usman | Latifabad Unit: 8 | Hyderabad |
5 | Afnan | Latifabad Unit: 6 | Hyderabad |
In Above Customer Table “Customer Id” is a primary key column.
Order Table
ORDER ID | ORDER ITEM | ORDER QUANTITY | PRICE OF 1 | CUSTOMER ID |
100 | Hardisk | 2 | 500 | 4 |
101 | Ram | 1 | 350 | 2 |
102 | Printer | 2 | 4000 | 3 |
103 | Monitor | 3 | 1500 | 4 |
104 | Mouse | 3 | 300 | 5 |
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