mysql表级约束语句

-- 创建表时定义约束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 0 AND age <= 120),
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES countries(country_id)
);

-- 表创建后添加约束
ALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120);

ALTER TABLE users
ADD CONSTRAINT fk_country FOREIGN KEY (country_id) REFERENCES countries(country_id);

-- 检查是否存在重复的主键值
SELECT user_id FROM users WHERE user_id = 1;

-- 如果存在重复值，可以选择删除重复记录或更新主键值
DELETE FROM users WHERE user_id = 1;

-- 或者更新主键值
UPDATE users SET user_id = 2 WHERE user_id = 1;

-- 检查引用表中是否存在该外键值
SELECT country_id FROM countries WHERE country_id = 1;

-- 如果不存在，可以选择插入缺失的值或更新外键值
INSERT INTO countries (country_id, country_name) VALUES (1, 'China');

-- 或者更新外键值
UPDATE users SET country_id = 2 WHERE country_id = 1;

-- 检查是否存在重复的唯一值
SELECT username FROM users WHERE username = 'admin';

-- 如果存在重复值，可以选择删除重复记录或更新唯一值
DELETE FROM users WHERE username = 'admin';

-- 或者更新唯一值
UPDATE users SET username = 'admin2' WHERE username = 'admin';