How to Properly Create Relational Tables Without Auto-Increment IDs?

SQL: Creating a Relational Table with 2 Different Auto Increments

Problem Overview

It's common to have two tables with auto-incremented IDs as primary keys. However, creating a third relational table to establish a relationship between these two tables often leads to errors. This is because either only one automatically-incremented column is allowed, or removing auto-increment from the initial tables prevents foreign key creation due to type matching issues.

Misconceptions about ID Fields and Primary Keys

It's important to address some fundamental misunderstandings:

• Auto-incremented IDs are not synonymous with primary keys in the technical or relational sense. A primary key should be a non-repeating, logical key extracted from the data, not an arbitrary number generated by the system.
• Declaring an ID field as a primary key does not magically transform it into a true primary key. It only ensures uniqueness within the table, but not uniqueness among tables.

Relational Table Requirements

Relational tables must have unique rows, which cannot be guaranteed by using auto-incremented IDs as primary keys. To create a relational table, we need to identify a real primary key composed of data attributes that uniquely identify each row.

Corrective Action

Step 1: Define a True Primary Key

Identify the data attributes that together uniquely distinguish rows in each table. Declare an unique constraint on these fields to prevent duplicate rows.

Step 2: Eliminate the ID Field

Once unique rows are ensured, the superfluous ID field (and its supporting index) becomes unnecessary and can be removed.

Step 3: Create the Associative Table

The third table, which joins the two primary tables, should have a composite primary key consisting of the primary keys from both parent tables. This ensures unique rows in the associative table.

Example

Suppose we have two tables: user and sport. To establish a relationship between them, we create a third table user_sport:

CREATE TABLE user (
  user_name CHAR(16) NOT NULL PRIMARY KEY,
  name_first CHAR(30) NOT NULL,
  name_last CHAR(30) NOT NULL,
  birth_date DATE NOT NULL
);

CREATE TABLE sport (
  sport_code CHAR(4) NOT NULL PRIMARY KEY,
  name CHAR(30) NOT NULL
);

CREATE TABLE user_sport (
  user_name CHAR(16) NOT NULL,
  sport_code CHAR(4) NOT NULL,
  start_date DATE NOT NULL,
  PRIMARY KEY (user_name, sport_code),
  FOREIGN KEY (user_name) REFERENCES user (user_name),
  FOREIGN KEY (sport_code) REFERENCES sport (sport_code)
);

In this example:

• user_name is the primary key of the user table.
• sport_code is the primary key of the sport table.
• (user_name, sport_code) is the composite primary key of the user_sport table, which uniquely identifies each row.

By following these principles, we can create relational tables with unique rows, ensure data integrity, and avoid the problems caused by using auto-incremented IDs as primary keys.

The above is the detailed content of How to Properly Create Relational Tables Without Auto-Increment IDs?. For more information, please follow other related articles on the PHP Chinese website!