SQL INNER JOIN / PRIMARY AND FOREIGN KEYS

 PRIMARY KEY

Definition:
    A primary key is a unique identifier for each record (row) in a table.
    It ensures that each row can be uniquely identified and retrieved.
    Primary keys must contain unique values and cannot have NULL values.
Key Points:
    A table can have only ONE primary key.
    The primary key can consist of a single column or multiple columns (composite key).
    It guarantees the distinctness of each row in the table.

FOREIGN KEY

Definition:
    A foreign key is a field (or collection of fields) in one table that refers to the primary key in another table.
    It establishes a relationship between two tables by linking them together.

Purpose:
    Foreign keys maintain referential integrity between related tables.
    They ensure that data remains consistent and connected across different tables.
Usage:
    Typically, a foreign key is used to enforce relationships between tables, especially in scenarios like parent-child     relationships or associations between entities.




CREATE TABLE tblcourses (
    crs_id INT PRIMARY KEY,
    crs_name VARCHAR(255)
);

CREATE TABLE tblstudents (
    stud_id VARCHAR(10) PRIMARY KEY,
    stud_name VARCHAR(255),
    stud_courseId INT,
    FOREIGN KEY (stud_courseId) REFERENCES tblcourses(crs_id)
);

CREATE TABLE tblsubjects (
    subject_code VARCHAR(255) PRIMARY KEY,
    subject_description VARCHAR(255)
);

CREATE TABLE tblstud_subjects (
    ssub_id INT PRIMARY KEY auto_increment,
    stud_id VARCHAR(10),
    subject_code VARCHAR(255),
    FOREIGN KEY (stud_id) REFERENCES tblstudents(stud_id),
    FOREIGN KEY (subject_code) REFERENCES tblsubjects(subject_code)
);

 

 MySQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

sYNTAX:

 

SELECTcolumn_name(s)

FROMtable1

INNER JOINtable2

ONtable1.column_name=table2.column_name;

 

 

 

 

 result:

 

 

 2.

output: