Foreign Key in SQL

What is Foreign Key in SQL?

In a relational database, a foreign key is a field or column that connects two tables. A foreign key in one table points to a primary key in another table, to put it another way. The FOREIGN KEY constraint prohibits erroneous data from being inserted in the foreign key column since it must be one of the entries in the parent database. It also prevents invalid data from being entered into the foreign key column.

SQL FOREIGN KEY on CREATE TABLE

When the "Orders" table is created, construct a foreign key on the "S_Id" column:

CREATE TABLE Orders
(  
O_Id int NOT NULL,  
Order_No  int NOT NULL,  
S_Id int,  
PRIMAY KEY (O_Id),  
FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)  
);  

CREATE TABLE Orders
(  
O_Id int NOT NULL PRIMAY KEY,  
Order_No int NOT NULL,  
S_Id int FOREIGN KEY REFERENCES persons (S_Id)  
)  ;  

To name a FOREIGN KEY constraint and define a FOREIGN KEY constraint on several columns, use the SQL syntax below:

CREATE TABLE Orders
(  
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)  
)  ;  

SQL FOREIGN KEY on ALTER TABLE

Use the following SQL to set a FOREIGN KEY constraint on the "PersonID" column once the "Orders" table has been created:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); 

To name a FOREIGN KEY constraint and define a FOREIGN KEY constraint on several columns, use the SQL syntax below:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); 

DROP a FOREIGN KEY Constraint

To remove a FOREIGN KEY constraint, run the following SQL:

ALTER TABLE Orders
DROP FOREIGN KEY Fkey_PerOrder; 

ALTER TABLE Orders
DROP CONSTRAINT Fkey_PerOrder; 

Difference Between Primary key and Foreign key in SQL

There are a few fundamental differences between primary and foreign keys in SQL.

🚀 The foreign key can be null, but not the primary key.
🚀 Primary keys are always unique, whereas foreign keys can be duplicated.
🚀A primary key is a field in one table that serves as the primary key in another, whereas a foreign key is a field in one table that serves as the primary key in another.
🚀 There may be more than one foreign key in the table, but there is only one main key.
🚀 The main key creates a clustered index by default; however, the foreign key does not construct an index by default, clustered or non-clustered.
🚀 You must manually create an index for a foreign key.