使用UNIQUE索引是否合适？学生表去重及建表相关咨询

Alright, let's refine your student table schema to enforce the uniqueness requirements you mentioned (no duplicate names/emails) and fix a potential issue with the phone field. Here's the complete, corrected SQL statement:

CREATE TABLE student(
    id INT UNSIGNED NOT NULL,
    campus_id SMALLINT UNSIGNED NOT NULL,
    first_career_id SMALLINT UNSIGNED NOT NULL,
    second_career_id SMALLINT UNSIGNED NULL,
    name VARCHAR(70) NOT NULL,
    password CHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL, -- Changed from TINYINT to VARCHAR since TINYINT can't hold valid phone numbers
    UNIQUE(id),
    UNIQUE(name),
    UNIQUE(email)
);

Key Notes & Explanations: #

• ID Uniqueness: The UNIQUE(id) constraint ensures your primary identifier stays unique (even without a formal PRIMARY KEY declaration, this hits the core requirement of a primary key).
• Name & Email Uniqueness: Added UNIQUE(name) and UNIQUE(email) to explicitly block duplicate entries for these fields, just as you specified.
• Phone Field Fix: I swapped phone TINYINT UNSIGNED NULL for VARCHAR(20) NULL because TINYINT UNSIGNED only supports values between 0-255—way too small for any real phone number (which might include country codes, hyphens, or parentheses). If you only want numeric digits, BIGINT UNSIGNED is another solid option.
• Nullable Fields: second_career_id and phone are marked as NULL, which makes perfect sense for students who don't have a second career or haven't provided their phone number yet.

If you need to tweak any constraints (like allowing duplicate names but keeping emails unique), feel free to adjust!

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