Define the primary and foreign keys in the accounts and payments tables

For the accounts and payments tables, primary and foreign keys are usually defined as follows:

• accounts:

  • Primary Key — a unique identifier for the account, such as account_id.
  • Foreign keys in this table are usually absent unless the account is linked to other entities.

• payments:

  • Primary Key — a unique identifier for the payment, such as payment_id.
  • Foreign Key — a reference to the account from which or to which the payment is made, such as account_id, referencing accounts.account_id.

Example of SQL definition:

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  account_name VARCHAR(100)
);

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  account_id INT,
  amount DECIMAL(10,2),
  payment_date DATE,
  FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

Thus, account_id in the payments table links the payment to a specific account.