Comparison Between Primary Key And Foreign Key With Examples
Summary: In this tutorial, we will learn the comparison between Primary Key and Foreign Key in SQL, and also we will understand the concept of both Primary and foreign keys. Furthermore, the Primary key and foreign key are SQL constraints that help us to manage the data and avoid any invalid transactions on it. let’s understand the primary key and foreign key in detail.
The primary key in SQL is limited to a single table and is put to uniquely identify the corresponding rows of a table. When we discuss Foreign keys, then we can have as many Foreign keys as we would like.
Furthermore, a foreign key comes to use when we require to link tables to one another and have data/information spread over multiple tables.
Let us discuss the concept of Primary and Foreign keys by understanding the definition, syntax, and examples of both keys.
What is a Primary Key in SQL?
- A Primary key is a SQL constraint. PK is a unique column we set in a table to easily recognize and locate data in queries. Thus, a table can have only one PK (primary key).
- The primary key column has a unique value and can’t store duplicate values (repeating values). A Primary key can never store NULL values.
- The primary key is a non-null or unique key that uniquely identifies every record or data in a table.
- Each database needs a unique identifier for every row of a table, and the primary key plays a significant role in identifying rows in the table uniquely.
- The primary key column does not store any duplicate values. It is also known as a minimal super key.
- Therefore, we can’t specify more than one PK in any relationship.
As shown in the given below the following illustration, the OrderID and VendorID columns in the Purchase.OrderVendor table forms a composite primary key constraint for this table. This makes sure that every row in the OrderVendor table has a unique combination of OrderID and VendorID.
This prevents the insertion of duplicate rows.
Important Note:
- A table can store or contain only one primary key constraint.
- A primary key can’t exceed sixteen (16) columns and includes a total key length of 900 bytes.
- Furthermore, the index generated by a PK constraint can’t cause the number of indexes on the table to exceed 999 nonclustered indexes & one clustered index.
- Furthermore, if clustered or non-clustered isn’t specified for a PK constraint, clustered is used if there’s no clustered index on the table.
- All columns defined within a primary key (PK) constraint should be defined as not null. Therefore, if nullability isn’t specified, all columns participating in a primary key constraint have their nullability set to not null.
- If a primary key is described on a CLR user-defined type column, the implementation of the type must support binary ordering.
Example of Primary Key
For illustration, in the case of an employee when identification needs to be done in the company, the employee Id of the employee plays the important role of Primary key.
Let us now understand the concept of the primary key and the Syntax of creating the table with the Primary key specified.
|
1 2 3 4 5 6 7 |
CREATE TABLE Employees ( emp_id int NOT NULL, emp_name varchar(50) NOT NULL, emp_department int, emp_city varchar(20) NOT NULL, PRIMARY KEY (emp_id) ); |
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tableName ( col1 int NOT NULL, col2 varchar(50) NOT NULL, col3 varchar(50) NOT NULL, col4 int, ……………. PRIMARY KEY (col1) ); |
What is a Foreign key in SQL?
A Foreign key (FK) is helpful when we connect two or more tables in order that data from both can be put to use parallelly.
Furthermore, a foreign key is a field or collection of fields during a table that refers to the Primary key of the other table. It’s responsible for managing the relationship or connection between the tables.
The table that contains the foreign secret is usually called the child table, and also the table whose primary key is being referred to by the foreign key is called the Parent Table.
Therefore, a table can reference a maximum of 253 other tables and columns as foreign keys. Furthermore, The SQL Server 2016 increases the limit for the no. of other tables & columns that can reference columns in a single table, from 253 to 10,000. (Requires at least 130 compatibility levels.) The increase has the following restrictions:
- Greater than 253 foreign key (FK) references are only supported for DELETE DML operations. Furthermore, UPDATE and MERGE SQL operations aren’t supported.
- A table with a foreign key (FK) reference to itself is still limited to 253 foreign key references.
- Greater than 253 foreign key references aren’t currently available for column store indexes, Stretch Database, memory-optimized tables, or partitioned foreign key tables.
Example of Foreign Key
When we talk about employees and the job profiles they have enrolled in, now if we try to store all the data in a single table, the problem of redundancy arises.
To solve this table, we make two tables, one the employee_detail table and the other the job_profile table. In the employee table, we store the details of employees and the job profile they have enrolled in.
Furthermore, and in the company_department table, we store all the details of the company department section. Here the profile acts as the Primary key for the company_department table whereas it acts as the Foreign key in the employee table.
Let us now look at the syntax of creating a table with a foreign key.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE childTable ( col1 int NOT NULL, col2 int NOT NULL, col3 varchar(50) NOT NULL,, col4 int, ………... PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES parentTable(parent_Primary_key) ); |
Primary Key vs. Foreign Key Comparison Chart
The given below Primary Key vs. Foreign Key Comparison Chart explains their main differences in a quick manner:
| Comparison Basis | Primary Key | Foreign Key |
|---|---|---|
| Basic | It is used to identify each record in the database table uniquely. | This is used to connect or related or links two tables together. Furthermore, it means the foreign key (FK) in one table refers to the primary key (PK) of another table. |
| NULL | The primary key column value can’t be NULL. | The foreign key (FK) column can accept a NULL value. |
| Count | A table can have only 1 (one) PK. | A table can have more than one foreign key (FK). |
| Indexing | The PK-primary key is a clustered index by default, which means that it is indexed automatically. | An FK-foreign key isn’t a clustered index by default. We can create clustered indexes manually. |
| Duplication | The primary key is a unique attribute; Furthermore, it can’t store any duplicate values in relation. | We can store duplicate values in the FK column. |
| Relationship | It can’t create a parent-child relationship in a table. | A foreign key can create a parent-child relationship in a table. |
| Temporary table | The PK-primary key constraint can be defined on the temporary tables. | A PK-foreign key constraint can’t be defined on the temporary tables. |
| Insertion | We can insert the values into the PK column without any limitation, whether it is present in an FK or not. | The value that isn’t present in the column of a primary key can’t be inserted into the referencing FK. |
| Deletion | The PK value can’t be removed from the table. furthermore, if you want to delete it, then make sure the referencing foreign key doesn’t contain its value. | The FK value can be removed from the table without bothering that it refers to the PK of another table. |
Key differences between Primary Key and Foreign Key
The given below following points justify/describe the differences b/w primary and foreign keys:
- A PK-Primary key constraint in the relational database acts as a unique identifier for every row in the table.
In contrast, an FK-foreign key constraint establishes a connection or relationship between two different tables to uniquely determine/identify a row of the equivalent/same table or another table. - The primary key column can’t store NULL values, whereas the FK-foreign key can accept more than one NULL value.
- Each table in a relational database can not define more than one PK-primary key while we can specify multiple FK-foreign keys in a table.
- Therefore, we can not remove the parent table’s PK-primary key value, which is referenced with an FK-foreign key column in the child table.
Furthermore, in contrast, we are able to delete the child table’s foreign key value even though they refer/confer with the parent table’s primary key. - A PK-primary key is a unique and non-null constraint, therefore no two rows can have the same/identical values for a PK-primary key attribute, whereas foreign key fields can store any duplicate values.
- We can insert the values into the PK-primary key column with no limitation.
Furthermore, in contrast, we’d like to confirm that the value is present in a PK-primary key column while inserting values in the FK-foreign key table. - We can implicitly define the PK-primary key constraint on temporary tables, whereas we have a tendency to cannot enforce foreign key constraints on temporary tables.
Conclusion:
In this article, In this article, you learned everything concerning such as What is a Primary Key in SQL, What is a Foreign key in SQL, we have made a comparison between PK-primary key and FK-foreign key constraints.
Primary Key vs. Foreign Key Comparison Chart clear the differences between both keys.
Furthermore, The primary key column always stores the unique value for each record in the table, whereas foreign key values can be duplicated. Both SQL constraint keys’ structures are the same, but their function and usages differ as the primary key identifies a record in a table or relation uniquely. And the FK-foreign key link or connected two tables together.
About Author
