How to identify the primary and foreign keys
The relationships between the tables in the accounts payable system
Two tables with a many-to-many relationship
Two tables with a one-to-one relationship
Description
- Each table should have a primary key that uniquely identifies each row. If possible, you should use an existing column for the primary key.
- The values of the primary keys should seldom, if ever, change. The values should also be short and easy to enter correctly.
- If a suitable column doesn’t exist for a primary key, you can create an ID column that is incremented by one for each new row as the primary key.
- If two tables have a one-to-many relationship, you may need to add a foreign key column to the table on the "many" side. The foreign key column must have the same data type as the primary key column it’s related to.
- If two tables have a many-to-many relationship, you can define a linking table to relate them. Then, each of the tables in the many-to-many relationship will have a one-to-many relationship with the linking table. The linking table doesn’t usually have a primary key.
- If two tables have a one-to-one relationship, they should be related by their primary keys. This type of relationship is typically used to improve performance. Then, columns with large amounts of data can be stored in a separate table.
Back
