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?
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);
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.
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.
The following points explain the differences between primary and foreign keys: