Working With Foreign Keys And Joins

Working With Foreign Keys And Joins

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

Structure of the table