Scribd
Upload
3 views4 pages

Key Attributes in MySQL

The document outlines key attributes in MySQL, defining various types of keys such as Primary Key, Foreign Key, Unique Key, Candidate Key, Alternate Key, Composite Key, and Super Key. Each key type serves specific purposes, such as uniquely identifying rows, maintaining data integrity, and linking tables. A summary table provides a quick reference for the characteristics of each key type.

Uploaded by

K Siva Naga Raju
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views4 pages

Key Attributes in MySQL

The document outlines key attributes in MySQL, defining various types of keys such as Primary Key, Foreign Key, Unique Key, Candidate Key, Alternate Key, Composite Key, and Super Key. Each key type serves specific purposes, such as uniquely identifying rows, maintaining data integrity, and linking tables. A summary table provides a quick reference for the characteristics of each key type.

Uploaded by

K Siva Naga Raju
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Key Attributes in MySQL

What are Keys in MySQL?

• Keys are columns (also called attributes) in a MySQL table that help to:

o Uniquely identify rows (records)

o Maintain data integrity

o Create relationships between tables

Types of Key Attributes in MySQL

1. Primary Key

• Uniquely identifies each row in the table

• Cannot be NULL

• Only one primary key per table

• Can be one or more columns

Example:

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(100)

);

2. Foreign Key

• Used to link two tables

• Refers to the Primary Key in another table

• Ensures referential integrity


Example:

CREATE TABLE Enrollments (

StudentID INT,

CourseID INT,

FOREIGN KEY (StudentID) REFERENCES Students(StudentID)

);

3. Unique Key

• Ensures that all values in a column are unique

• Allows NULL values (unlike Primary Key)

• A table can have multiple unique keys

Example:

CREATE TABLE Users (

UserID INT PRIMARY KEY,

Email VARCHAR(100) UNIQUE

);

4. Candidate Key

• Any column (or combination of columns) that can uniquely identify a row

• Multiple candidate keys can exist in a table

• One of them becomes the Primary Key

Example:

• StudentID and Email are candidate keys

• If StudentID is chosen as the Primary Key, Email becomes an Alternate Key


5. Alternate Key

• A candidate key not chosen as the primary key

• Still unique and can be used for searching or indexing

Example:

-- If StudentID is Primary Key, Email is an Alternate Key

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Email VARCHAR(100) UNIQUE

);

6. Composite Key

• A key made from two or more columns

• Used when a single column is not enough to identify a row uniquely

Example:

CREATE TABLE Enrollments (

StudentID INT,

CourseID INT,

PRIMARY KEY (StudentID, CourseID)

);

7. Super Key

• Any combination of attributes that can uniquely identify a row

• Includes Primary Key, Candidate Key, and Composite Key

• May have extra columns (not minimal)

Example:

• {StudentID, Name} is a Super Key (but not a Candidate Key)


Summary Table – Quick Revision

Key Type Description

Primary Key Uniquely identifies each row; no NULLs; only one per table

Foreign Key Refers to primary key in another table

Unique Key Unique values; allows NULLs; can be many in a table

Candidate Key Possible primary keys

Alternate Key Candidate keys not chosen as primary

Composite Key Primary key made of two or more columns

Super Key Any combination that uniquely identifies rows

You might also like