7. Foreign Key Constraint

FOREIGN KEY 

• A foreign key is a column or set of columns in one table that refers to the primary key or a unique key in another table.
• It is used to establish a relationship between two tables in a relational database.
• The foreign key constraint ensures that the values in the foreign key column of one table match the value in the primary key or unique key column of another table.
• Prevent invalid data from being inserted into the database, maintaining the referential integrity of the database.
• Foreign keys can be used to create different types of relationships between tables, such as one-to-one, one-to-many or many-to-many. 
• The referenced table and column must already exist and have a primary key or unique constraint defined on them

SYNTAX

While Creating a Table

CREATE TABLE table_name(

    column 1 datatype,

    column 2 datatype,

    column n datatype,

    FOREIGN KEY (Foreign_key_column)

REFERENCES

referenced_table(referenced_column)

   );

    E.g.

       CREATE TABLE Customers (

      id INT PRIMARY KEY,

      name VARCHAR(50),

      email VARCHAR(50)

      );

      CREATE TABLE Orders (

      id INT PRIMARY KEY,

      order_date DATE,

      customer_id INT,

      FOREIGN KEY (customer_id)

      REFERENCES Customers(id)

      );

            Here customer_id of Orders table is referring to the id of Customer table which ensure that only the customer whose id are recorded in Customer table can be recorded in Orders table

After the Table is Created

   ALTER TABLE Foreign key table    ADD CONSTRAINT

fk_constraint_name     FOREIGN KEY (foreign_key_column)     REFERENCES

primary_key_table_name

(primary_key_column);

E.g.

    

    ALTER TABLE Orders     ADD CONSTRAINT fk_customer     FOREIGN KEY (customer_id)

REFERENCES Customers(id);