数据库设计：创建个人信息、教育背景、工作经历、技能和语言数据表并设置约束

创建数据库表并设置约束

本文将演示如何使用 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)
);

常见错误及解决方法

ALTER TABLE personal_info ALTER COLUMN gender SET DEFAULT 'unknown'; 显示语法错误

该错误是因为 SET DEFAULT 语法在 ALTER TABLE 语句中使用不正确。正确的语法应该是 ALTER TABLE personal_info ALTER COLUMN gender SET DEFAULT 'unknown';
表 'education' 的列 'end_date' 的列 CHECK 约束引用了另一列。消息 1750，级别 16，状态 0，第 11 行

这是因为在创建 education 表的时候，end_date 列的 CHECK 约束直接引用了 start_date 列，导致了错误。

解决方法：

在创建 education 表的时候，需要将 start_date 和 end_date 列的 CHECK 约束分别改为：
```
CHECK (end_date > start_date)
```
这样才能避免列 CHECK 约束引用了另一列的错误。

设置数据表约束

-- 为 personal_info 表设置主键
ALTER TABLE personal_info ADD CONSTRAINT pk_personal_info PRIMARY KEY (id);

-- 为 education 表设置主键和外键
ALTER TABLE education ADD CONSTRAINT pk_education PRIMARY KEY (id);
ALTER TABLE education ADD CONSTRAINT fk_education_personal_info FOREIGN KEY (id) REFERENCES personal_info(id);

-- 为 work_experience 表设置主键和外键
ALTER TABLE work_experience ADD CONSTRAINT pk_work_experience PRIMARY KEY (id);
ALTER TABLE work_experience ADD CONSTRAINT fk_work_experience_personal_info FOREIGN KEY (id) REFERENCES personal_info(id);

-- 为 skills 表设置主键
ALTER TABLE skills ADD CONSTRAINT pk_skills PRIMARY KEY (id);

-- 为 languages 表设置主键
ALTER TABLE languages ADD CONSTRAINT pk_languages PRIMARY KEY (id);

-- 为 personal_info 表的 id_number 列设置唯一约束
ALTER TABLE personal_info ADD CONSTRAINT uk_personal_info_id_number UNIQUE (id_number);

-- 为 personal_info 表的 id 列设置自动编号的标识列
ALTER TABLE personal_info ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

-- 为 skills 表的 proficiency 列设置检查约束
ALTER TABLE skills ADD CONSTRAINT chk_skills_proficiency CHECK (proficiency IN ('beginner', 'intermediate', 'advanced'));

-- 为 languages 表的 proficiency 列设置检查约束
ALTER TABLE languages ADD CONSTRAINT chk_languages_proficiency CHECK (proficiency IN ('basic', 'conversational', 'fluent'));

总结

本文介绍了如何使用 SQL 代码创建数据库表并设置主键、外键、唯一约束、默认值约束、检查约束和自动编号的标识列等，并解决创建数据表时遇到的常见错误。希望本文能够帮助您更好地理解数据库设计的基本概念和操作。