SQL数据库表设计：主键、外键、约束和自动编号

SQL 数据库表设计：主键、外键、约束和自动编号

本文档使用 SQL 代码演示如何在数据库表中设置主键、外键、唯一约束、默认值约束、检查约束和自动编号的标识列。

数据库表结构:

CREATE TABLE personal_info (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  gender VARCHAR(10),
  birthdate DATE,
  id_number VARCHAR(20),
  phone VARCHAR(20),
  email VARCHAR(50)
);

CREATE TABLE education (
  id INT PRIMARY KEY,
  degree VARCHAR(50),
  major VARCHAR(50),
  school VARCHAR(50),
  start_date DATE,
  end_date DATE
);

CREATE TABLE work_experience (
  id INT PRIMARY KEY,
  company VARCHAR(50),
  position VARCHAR(50),
  start_date DATE,
  end_date DATE,
  job_description VARCHAR(500)
);

CREATE TABLE skills (
  id INT PRIMARY KEY,
  skill_name VARCHAR(50),
  proficiency VARCHAR(20)
);

CREATE TABLE languages (
  id INT PRIMARY KEY,
  language_name VARCHAR(50),
  proficiency VARCHAR(20)
);

设置约束:

personal_info 表:

-- 主键约束
ALTER TABLE personal_info ADD CONSTRAINT PK_personal_info_id PRIMARY KEY (id);
-- 唯一约束
ALTER TABLE personal_info ADD CONSTRAINT UQ_personal_info_id_number UNIQUE (id_number);
-- 默认值约束
ALTER TABLE personal_info ALTER COLUMN phone SET DEFAULT 'N/A';
-- 检查约束
ALTER TABLE personal_info ADD CONSTRAINT CHK_personal_info_gender CHECK (gender IN ('Male', 'Female'));
-- 自动编号的标识列
ALTER TABLE personal_info ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

education 表:

-- 主键约束
ALTER TABLE education ADD CONSTRAINT PK_education_id PRIMARY KEY (id);
-- 外键约束
ALTER TABLE education ADD CONSTRAINT FK_education_personal_info_id FOREIGN KEY (id) REFERENCES personal_info(id) ON DELETE CASCADE;
-- 默认值约束
ALTER TABLE education ALTER COLUMN start_date SET DEFAULT '1900-01-01';
ALTER TABLE education ALTER COLUMN end_date SET DEFAULT '1900-01-01';
-- 检查约束
ALTER TABLE education ADD CONSTRAINT CHK_education_degree CHECK (degree IN ('Bachelor', 'Master', 'PhD'));
-- 自动编号的标识列
ALTER TABLE education ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

work_experience 表:

-- 主键约束
ALTER TABLE work_experience ADD CONSTRAINT PK_work_experience_id PRIMARY KEY (id);
-- 外键约束
ALTER TABLE work_experience ADD CONSTRAINT FK_work_experience_personal_info_id FOREIGN KEY (id) REFERENCES personal_info(id) ON DELETE CASCADE;
-- 默认值约束
ALTER TABLE work_experience ALTER COLUMN start_date SET DEFAULT '1900-01-01';
ALTER TABLE work_experience ALTER COLUMN end_date SET DEFAULT '1900-01-01';
-- 自动编号的标识列
ALTER TABLE work_experience ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

skills 表:

-- 主键约束
ALTER TABLE skills ADD CONSTRAINT PK_skills_id PRIMARY KEY (id);
-- 外键约束
ALTER TABLE skills ADD CONSTRAINT FK_skills_personal_info_id FOREIGN KEY (id) REFERENCES personal_info(id) ON DELETE CASCADE;
-- 自动编号的标识列
ALTER TABLE skills ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

languages 表:

-- 主键约束
ALTER TABLE languages ADD CONSTRAINT PK_languages_id PRIMARY KEY (id);
-- 外键约束
ALTER TABLE languages ADD CONSTRAINT FK_languages_personal_info_id FOREIGN KEY (id) REFERENCES personal_info(id) ON DELETE CASCADE;
-- 自动编号的标识列
ALTER TABLE languages ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

说明：

• PRIMARY KEY 约束用于定义表的主键，它可以确保列中每个值都是唯一的，并且不能为空。
• UNIQUE 约束用于定义唯一约束，它确保列中每个值都是唯一的，但可以为空。
• FOREIGN KEY 约束用于定义外键，它将一个表中的列与另一个表中的主键相关联，以维护数据完整性。
• DEFAULT 约束用于为列定义默认值，当没有指定值时，该默认值将被使用。
• CHECK 约束用于定义检查约束，它用于确保列中的值满足特定的条件。
• GENERATED ALWAYS AS IDENTITY 语法用于定义自动编号的标识列。

注意：

• SET IDENTITY 语法在一些数据库系统中可能存在语法错误。请使用 GENERATED ALWAYS AS IDENTITY 或 AUTO_INCREMENT 等语法替代。
• 上述 SQL 代码需要根据您使用的数据库系统进行调整。

通过以上步骤，您就可以成功设置数据库中各个数据表的主键、外键、唯一约束、默认值约束、检查约束和自动编号的标识列等，从而确保数据的完整性和一致性。