What are Primary and Foreign Keys?

Primary and foreign keys are the most important concepts in relational databases. Primary keys are unique identifiers for records in a table. They are used to enforce uniqueness and are created when a table is created. Foreign keys are used to link tables together, creating relationships between them.

How to Create a Primary Key?

To create a primary key in MySQL, you need to specify the PRIMARY KEY constraint when creating a table. For example:

CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50) );

The above example creates a table called "customers" with an "id" column as the primary key.

How to Create a Foreign Key?

To create a foreign key in MySQL, you need to specify the FOREIGN KEY constraint when creating a table. For example:

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) );

The above example creates a table called "orders" with a "customer_id" column as the foreign key. It references the "id" column in the "customers" table.

How to Alter a Table to Add Primary and Foreign Keys?

You can alter an existing table in MySQL to add primary and foreign keys. For example:

ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (id);

The above example adds a primary key constraint to the "id" column in the "customers" table. Similarly, you can add a foreign key constraint:

ALTER TABLE orders ADD CONSTRAINT orders_fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);

The above example adds a foreign key constraint to the "customer_id" column in the "orders" table. It references the "id" column in the "customers" table.

Conclusion

Using primary and foreign keys in a MySQL database ensures data integrity and maintains relationships between tables. By creating and altering tables with primary and foreign key constraints, you can build complex systems that interact with each other, making your application more robust and reliable.