Foreign Keys in database

Introduction

You will be probably knowing that working with foreign keys is quite difficult as people get frustrated in adding the data in the table because of the restrictions that foreign key provides, but the positive point of the foreign key is that it provides SECURITY i.e it avoids adding of duplicate data in the table. Let's discuss what are the properties of the Foreign key and how can we add it?

Points to be remembered while adding foreign keys

1. While adding the foreign key in the table the datatype should be exactly similar to that of the primary key.
2. The Foreign key is required to be mapped to other tables primary key.
3. Data should be present in the foreign key column and it should contain some same value of primary key so that you can easily attach foreign key.
4. If the data is not present in the foreign key column or some row of the foreign key column is left null, You cannot attach foreign key constraint after creating or before creating the table.

ALTER TABLE ORDERS
ADD COLUMN CUSTOMER_ID INT UNSIGNED NOT NULL
ADD CONSTRAINT FK_CUST_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID);

CREATE TABLE CUSTOMER(
  CUSTOMER_ID INT UNSINGED NOT NULL PRIMARY KEY AUTO INCREMENT
  CUSTOMER_NAME VARCHAR(20)
  CUSTOMER_PHONE INT UNSIGNED
  CUSTOMER_STATE VARCHAR(20)
  CUSTOMER_CITY VARCHAR(20)
  CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE ORDERS(
  ORDERS_ID INT NOT NULL UNSINGED PRIMARY KEY AUTO INCREMENT
  ORDERS_DATE VARCHAR(20)
  SHIPPED_DATE VARCHAR(20)
  STATUS VARCHAR(20)
  CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);

Structure of the table

Foreign key and primary key in sql

Primary Key

The primary key is a unique or non-null key that uniquely identifies every record in a table or relation. Each database needs a unique identifier for every row of a table, and the primary key plays a vital role in identifying rows in the table uniquely. The primary key column can't store duplicate values. It is also called a minimal super key; therefore, we cannot specify more than one primary key in any relationship.

A primary key is used to ensure data in the specific column is unique. It is a column cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

A primary key generally focuses on the uniqueness of the table. It is a column or a set of columns that uniquely distinguishes every row in the database. It means it should not have any duplicate value. Also, it doesn’t contain a NULL value.

Foreign Key

The foreign key is a group of one or more columns in a database to uniquely identify another database record in some other table to maintain the referential integrity. It is also known as the referencing key that establishes a relationship between two different tables in a database. A foreign key always matches the primary key column in another table. It means a foreign key column in one table refers to the primary key column of another table. A foreign key is beneficial in relational database normalization, especially when we need to access records from other tables.

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.

A foreign key is generally used to build a relationship between the two tables. The major purpose of the foreign key is to sustain data integrity between two separate instances of an entity.

Key differences between Primary Key and Foreign Key

The following points explain the differences between primary and foreign keys:

1. A primary key constraint in the relational database acts as a unique identifier for every row in the table. In contrast, a foreign key constraint establishes a relationship between two different tables to uniquely identify a row of the same table or another table.
2. The primary key column does not store NULL values, whereas the foreign key can accept more than one NULL value.
3. Each table in a relational database can't define more than one primary key while we can specify multiple foreign keys in a table.
4. We can't remove the parent table's primary key value, which is referenced with a foreign key column in the child table. In contrast, we can delete the child table's foreign key value even though they refer to the parent table's primary key.
5. A primary key is a unique and non-null constraint, so no two rows can have identical values for a primary key attribute, whereas foreign key fields can store duplicate values.
6. We can insert the values into the primary key column without any limitation. In contrast, we need to ensure that the value is present in a primary key column while inserting values in the foreign key table.
7. We can implicitly define the primary key constraint on temporary tables, whereas we cannot enforce foreign key constraints on temporary tables.