MySQL Workbench外键报错求助：移动运营商数据库表设计问题

Hey there! I totally get the frustration of dealing with unexpected foreign key errors when you're just starting out with SQL—and MySQL Workbench's auto-adding features can definitely throw a wrench in things (you can disable that auto-generate foreign key option in the table editor settings if you want to take full control). Let's walk through the issues in your code and fix them to meet your assignment's requirements.

Key Issues in Your Original Code #

Let's break down what's causing those errors and design flaws:

1. Spelling Mistake: In the operatori table, you referenced località(codice)—but your table is named localita (no accent mark). MySQL can't find the table with the accent, so this throws a foreign key error.
2. Incorrect Data Types: Using varchar(20) for numeric values (like营业额、通话费用、时长、单词数) is bad practice—use numeric types instead to enable calculations and avoid data corruption.
3. Wrong Primary Key Designs:
   • Your ChiamateFonia/ChiamateSms primary keys only use datechiamate and orario, but the assignment requires same user can't make multiple calls in the same date + minute. You need to include the caller (chiamante) in the primary key to enforce this rule.
   • The utenze table's primary key is a confusing composite key—per the assignment, a user's phone number is their unique identifier, so the primary key should just be the user's phone number.
4. Invalid Foreign Key Associations:
   • localita has a composite primary key (codicecella,codice), but operatori only references codice—foreign keys must match the full primary/unique key of the parent table. We'll adjust the localita schema to fix this.
   • utenze references numeroidchiamato as part of its primary key, but this is a called number (another user's phone), which should be a foreign key back to utenze instead of part of the primary key.
5. Redundant Field: chiamateeffetuate in utenze is unnecessary—you can calculate the number of calls a user made by querying the call tables later, so storing it creates data inconsistency risks.

Corrected SQL Code #

Here's the revised schema that aligns with your assignment requirements and fixes all errors:

-- 1. Locations & Base Stations Table
-- Each location has a unique ID; base station IDs are unique within their location
CREATE TABLE localita (
    codice_locale VARCHAR(20) PRIMARY KEY, -- Location unique identifier
    nome VARCHAR(50) NOT NULL, -- Location name
    provincia VARCHAR(50) NOT NULL, -- Province
    regione VARCHAR(50) NOT NULL, -- Region
    codicecella VARCHAR(20), -- Base station ID
    UNIQUE KEY unique_cella_per_locale (codice_locale, codicecella) -- Enforce unique base station per location
);

-- 2. Operators Table
CREATE TABLE operatori (
    idoperatore VARCHAR(20) PRIMARY KEY, -- Operator unique ID
    fatturato DECIMAL(15,2) NOT NULL, -- Annual turnover (numeric type for calculations)
    sede VARCHAR(20) NOT NULL,
    FOREIGN KEY (sede) REFERENCES localita(codice_locale) -- Links to location ID
);

-- 3. Users Table
-- User's phone number is their unique identifier
CREATE TABLE utenze (
    numero_telefono VARCHAR(20) PRIMARY KEY, -- User's unique phone number (identifier)
    nome_utente VARCHAR(50) NOT NULL, -- User's name
    idoperatore VARCHAR(20) NOT NULL, -- Contracted operator
    costo_per_sec DECIMAL(6,4) NOT NULL, -- Per-second call cost (numeric type)
    FOREIGN KEY (idoperatore) REFERENCES operatori(idoperatore)
);

-- 4. Telephony Calls Table (inherits common call fields, adds duration)
CREATE TABLE ChiamateFonia (
    chiamante VARCHAR(20) NOT NULL,
    data_chiamata DATE NOT NULL,
    ora INT NOT NULL, -- Hour (0-23)
    minuto INT NOT NULL, -- Minute (0-59)
    chiamato VARCHAR(20) NOT NULL,
    costo DECIMAL(8,4) NOT NULL, -- Total call cost
    durata_sec INT NOT NULL, -- Call duration in seconds
    codicecella VARCHAR(20) NOT NULL, -- Base station that initiated the call
    codice_locale VARCHAR(20) NOT NULL,
    -- Enforce unique call per user + date + minute
    PRIMARY KEY (chiamante, data_chiamata, minuto),
    -- Foreign key links
    FOREIGN KEY (chiamante) REFERENCES utenze(numero_telefono),
    FOREIGN KEY (chiamato) REFERENCES utenze(numero_telefono),
    FOREIGN KEY (codice_locale, codicecella) REFERENCES localita(codice_locale, codicecella)
);

-- 5. SMS Calls Table (inherits common call fields, adds word count)
CREATE TABLE ChiamateSms (
    chiamante VARCHAR(20) NOT NULL,
    data_chiamata DATE NOT NULL,
    ora INT NOT NULL,
    minuto INT NOT NULL,
    chiamato VARCHAR(20) NOT NULL,
    costo DECIMAL(8,4) NOT NULL, -- Total SMS cost (calculated as word count * per-second rate)
    num_parole INT NOT NULL, -- Number of words in SMS
    codicecella VARCHAR(20) NOT NULL,
    codice_locale VARCHAR(20) NOT NULL,
    -- Enforce unique SMS per user + date + minute
    PRIMARY KEY (chiamante, data_chiamata, minuto),
    -- Foreign key links
    FOREIGN KEY (chiamante) REFERENCES utenze(numero_telefono),
    FOREIGN KEY (chiamato) REFERENCES utenze(numero_telefono),
    FOREIGN KEY (codice_locale, codicecella) REFERENCES localita(codice_locale, codicecella)
);

Quick Notes on the Fixes: #

• Data Types: Switched numeric values to DECIMAL (for currency/decimal values) and INT (for whole numbers like duration, word count) to support calculations required by your assignment (like converting SMS word count to call seconds).
• Primary Keys: Updated call tables to use (chiamante, data_chiamata, minuto) as the primary key—this strictly enforces the rule that a user can't make multiple calls/SMS in the same minute on the same date.
• Foreign Keys: Fixed all associations to match parent table keys (e.g., linking base stations to both location ID and station ID, since stations are unique per location).
• Schema Clarity: Renamed some fields to be more readable (like numero_telefono instead of numeroidchiamante) to avoid confusion.

内容的提问来源于stack exchange，提问作者Messy