Differences between foreign and primary keys in a database?

1. Definition:
- Foreign key: A foreign key is a field in a database table that is used to establish a relationship with another table. It is used to link two tables together by referencing the primary key of another table.
- Primary key: A primary key is a unique identifier for each record in a database table. It ensures that each record in the table is unique and can be used to uniquely identify each record.

2. Purpose:
- Foreign key: The primary purpose of a foreign key is to establish a relationship between two tables in a database. It ensures referential integrity and helps maintain data consistency.
- Primary key: The primary key is used to uniquely identify each record in a table. It helps in retrieving and updating specific records in the table.

3. Uniqueness:
- Foreign key: A foreign key does not have to be unique within a table. It can be repeated in multiple records in a table.
- Primary key: A primary key must be unique within a table. It cannot be repeated in any other record in the table.

4. Constraints:
- Foreign key: Foreign keys can have NULL values, meaning they can be empty in some records.
- Primary key: Primary keys cannot have NULL values. Every record in the table must have a value for the primary key.

5. Relationship:
- Foreign key: Foreign keys establish a relationship between two tables, typically linking a column in one table to the primary key of another table.
- Primary key: The primary key uniquely identifies each record in a table and is used as a reference point for foreign keys in other tables.

In summary, foreign keys are used to establish relationships between tables, while primary keys are used to uniquely identify records within a table. Foreign keys do not have to be unique and can have NULL values, while primary keys must be unique and cannot have NULL values.

Primary Key

- Definition: A unique identifier for each row in a table.
- Properties:
- No two rows can have the same primary key value.
- Primary keys are often generated automatically by the database system.
- They ensure data integrity and prevent duplicate entries.
- Example: A customer ID in a customers table.

Foreign Key

- Definition: A reference to a primary key in another table.
- Properties:
- A foreign key value in one table must match the primary key value in another table.
- Foreign keys create relationships between tables, connecting data across databases.
- They ensure data consistency and prevent orphaned records.
- Example: An order ID in an orders table that refers to a customer ID in the customers table.

Key Differences

| Feature | Primary Key | Foreign Key |
|---|---|---|
| Purpose | Uniquely identify rows | Establish relationships between tables |
| Value | Unique for each row | Refers to a primary key in another table |
| Relationship | Self-referential | Cross-referential |
| Data Integrity | Ensures no duplicates | Prevents orphaned records |
| Generation | Often auto-generated | Manually defined |
| Cardinality | 1:N (one to many) | N:1 (many to one) or 1:1 (one to one) |