Primary and Foreign Keys in MySQL

Página 8

Primary and foreign keys are fundamental concepts in MySQL that play a crucial role in organizing and managing data. Let's start with a basic understanding of these terms.

Primary Key

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. In other words, there cannot be two rows in the same table that have the same primary key value. This guarantees the uniqueness of the data in the table. Also, a table can have only one primary key.

For example, in a "Customers" table, "Customer ID" can be used as a primary key, as each customer will have a unique ID.

To define a primary key in MySQL, you can use the following syntax:

CREATE TABLE Customers (
    CustomerID int NOT NULL,
    CustomerName varchar(255) NOT NULL,
    ContactName varchar(255),
    Country varchar(255),
    PRIMARY KEY (CustomerID)
);

In this example, the column 'CustomerID' is defined as the primary key of the table 'Customers'.

Foreign Key

A foreign key is a column or set of columns in a table that is used to establish and enforce a link between data in two tables. This link is created between the primary key of one table and the foreign key of another table.

For example, in an "Orders" table, "Customer ID" can be used as a foreign key to reference "Customers".

To define a foreign key in MySQL, you can use the following syntax:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example, the column 'CustomerID' is defined as the foreign key in the table 'Orders' that references the primary key 'CustomerID' in the table 'Customers'.

Importance of Primary and Foreign Keys

Primary and foreign keys are vital to effective database management. They ensure data integrity by preventing duplicates and ensuring related data is properly linked. In addition, they also allow for more efficient and accurate queries.

Primary keys allow you to retrieve data from a table quickly and easily. On the other hand, foreign keys allow you to create relationships between tables, which is crucial for relational databases.

In summary, primary and foreign keys are essential tools in database design that help ensure data integrity and efficiency. They are the backbone of any relational database, and therefore understanding how they work when working with MySQL is crucial.

Next page of the Free Ebook:

9Relationships between tables in MySQL