In this article, we can learn the difference between Primary Key and Foreign Key in MySQL Database.
What are Keys?
Keys are attribute that helps you to identify a row(tuple) in a relation(table). They allow you to find the relationship between the two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. The database key is helpful for finding a unique record or row from the table.
What is Database Relationship?
Database relationship is associations between one or more tables that are created using join statements. It is used to efficiently retrieve data from the database.
There are primarily three types of relationships
Difference between Primary Key and Foreign Key in MySQL Database
What is Primary Key?
Primary key constrain is a column or group of columns that uniquely identifies every row in the table of the relational database management system. It cannot be a duplicate, meaning the same value should not appear more than once in the table.
A table can have more than one primary key. The primary key can be defined at the column or the table level. If you create a composite primary key, it should be defined at the table level.
CREATE TABLE <Table-Name> ( Column1 datatype, Column2 datatype, PRIMARY KEY (Column-Name) . );
- Table_Name is the name of the table you have to create.
- Column_Name is the name of the column having the primary key.
What is Foreign Key?
A foreign key is a column (or combination of columns) in a table whose values must match the values of a column in some other table. foreign key constraints enforce referential integrity, which essentially says that if column value A refers to column value B, then column value B must exist.
CREATE TABLE <Table Name>( column1 datatype, column2 datatype, constraint (name of constraint) FOREIGN KEY [column1, column2...] REFERENCES [primary key table name] (List of primary key table column) ...);
- The parameter Table Name indicates the name of the table that you are going to create.
- The parameters column1, column2… depicts the columns that need to be added to the table.
- Constraint denotes the name of constraint you are creating.
- References indicate a table with the primary key.
Difference between Primary Key and Foreign Key
|Primary Key||Foreign Key|
|primary key constrain is a column or group of columns that uniquely identifies every row in the table of the relational database management system.||foreign key is a column that creates a relationship between two tables.|
|primary key helps you to uniquely identify a record in the table.||the field in the table that is a primary key of another table.|
|Primary Key never accepts null values.||foreign key may accept multiple null values.|
|The primary key is a clustered index, and data in the DBMS table are physically organized in the sequence of the clustered index.||foreign key cannot automatically create an index, clustered, or non-clustered.|
|The value of the primary key can’t be removed from the parent table.||The value of foreign key value can be removed from the child table.|
|You can define the primary key implicitly on the temporary tables.||You cannot define foreign keys on the local or global temporary tables.|
|You can have the single Primary key in a table.||You can have multiple foreign keys on a table.|
|The primary key is a clustered index.||By default, it is not a clustered index.|
|No two rows can have any identical values for a primary key.||foreign key can contain duplicate values.|
|There is no limitation in inserting the values into the table column.||While inserting any value in the foreign key table, ensure that the value is present in a column of a primary key.|