Primary and Foreign Key in SQL With Examples
Keeping you updated with latest technology trends, Join DataFlair on Telegram
1. Primary and Foreign Key in SQL
Today, in this tutorial of Primary and Foreign Key in SQL, we will see the definitions of Primary and Foreign Key in SQL. Moreover, we will discuss SQL Primary key with example and also see SQL create table Primary Key. Along with this, we will discuss SQL Foreign Key with example and SQL create table Foreign Key. At last, we will learn a difference between Primary Key and Foreign Key in SQL.
So, let us start the tutorial of the Primary and Foreign key in SQL.
2. Primary Key and Foreign Key Definition
A SQL Primary Key is a field in a table which remarkably distinguishes each line/record in a database table. Primary keys must contain unique values. A primary key segment can’t have NULL qualities.
Do you know about Null values in SQL?
A SQL Foreign Key is a key used to connect two tables together. This is now and again likewise called as a referencing key.
3. What is a Primary Key?
A table can have just a single primary key, which may comprise of single or different fields. At the point when numerous fields are utilized as a primary key, they are known as a composite key.
In the event that a table has a primary key characterized on any field(s), at that point you can’t have two records having a similar estimation of that field(s).
- Create Primary Key SQL Server
The following SQL Primary Key example sets ID as a primary key in a CUSTOMERS table.
Make TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , Salary DECIMAL (18, 2), PRIMARY KEY (ID) );
To make a SQL PRIMARY KEY limitation on the “ID” segment when the CUSTOMERS table as of now exists, utilize the accompanying SQL language structure −
Have a look at SQL Wildcards Characters With Example
ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
NOTE − If you utilize the ALTER TABLE articulation to include a primary key, the primary key column(s) ought to have just been announced to not contain NULL qualities (when the table was first made).
For characterizing a PRIMARY KEY imperative on different sections, utilize the SQL language structure given underneath.
Make TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , Salary DECIMAL (18, 2), PRIMARY KEY (ID, NAME) );
To make a PRIMARY KEY imperative on the “ID” and “NAMES” segments when CUSTOMERS table as of now exists, utilize the accompanying SQL sentence structure.
Change TABLE CUSTOMERS Include CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);
Erase Primary Key
You can clear the primary key requirements from the table with the sentence structure given underneath.
Do you know about SQL Index
ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;
4. What is Foreign Key?
A Foreign Key is a segment or a mix of segments whose qualities coordinate a Primary Key in an alternate table.
The connection between 2 tables coordinates the Primary Key in one of the tables with a Foreign Key in the second table.
Example of SQL Foreign Key –
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID) );
On the off chance that the ORDERS table has just been made and the foreign key has not yet been set, the utilization of the linguistic structure for indicating a foreign key by adjusting a table.
You must read SQL Expressions
- Adjust TABLE ORDERS
Include FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
DROP a FOREIGN KEY Constraint
- To drop a FOREIGN KEY constraint, utilize the accompanying SQL language structure.
Adjust TABLE ORDERS DROP FOREIGN KEY;
So, this was all in Primary and Foreign Key in SQL. Hope you like our explanation.
5. Conclusion: Primary Key and Foreign Key in SQL
Hence, in this tutorial of the Primary key and Foreign Key in SQL, we learned about the SQL Primary Key and SQL Foreign Key. moreover, we looked at how SQL Create Table Primary Key and SQL Create table Foreign Key. Also, we discussed the Primary Key and Foreign Key in SQL examples. Still, if you have any doubt regarding Primary Key and Foreign Key in SQL, ask in the comment tab. If this article helps you, inform us through comments.
See also –
SQL Server Transaction