SQL 数据库设计：个人信息、教育、工作经验、技能和语言表

以下是完整的 SQL 代码，包括主键、外键、唯一约束、默认值约束、检查约束和自动编号的标识列的设置：

CREATE TABLE personal_info (
  id INT PRIMARY KEY IDENTITY(1,1),
  name VARCHAR(50) NOT NULL,
  gender VARCHAR(10) DEFAULT 'unknown' CHECK (gender IN ('male', 'female', 'unknown')),
  birthdate DATE,
  id_number VARCHAR(20) UNIQUE,
  phone VARCHAR(20),
  email VARCHAR(50) UNIQUE
);

CREATE TABLE education (
  id INT PRIMARY KEY IDENTITY(1,1),
  personal_info_id INT REFERENCES personal_info(id),
  degree VARCHAR(50) NOT NULL,
  major VARCHAR(50) NOT NULL,
  school VARCHAR(50) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL CHECK (end_date >= start_date)
);

CREATE TABLE work_experience (
  id INT PRIMARY KEY IDENTITY(1,1),
  personal_info_id INT REFERENCES personal_info(id),
  company VARCHAR(50) NOT NULL,
  position VARCHAR(50) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL CHECK (end_date >= start_date),
  job_description VARCHAR(500)
);

CREATE TABLE skills (
  id INT PRIMARY KEY IDENTITY(1,1),
  personal_info_id INT REFERENCES personal_info(id),
  skill_name VARCHAR(50) NOT NULL,
  proficiency VARCHAR(20) NOT NULL CHECK (proficiency IN ('beginner', 'intermediate', 'advanced'))
);

CREATE TABLE languages (
  id INT PRIMARY KEY IDENTITY(1,1),
  personal_info_id INT REFERENCES personal_info(id),
  language_name VARCHAR(50) NOT NULL,
  proficiency VARCHAR(20) NOT NULL CHECK (proficiency IN ('beginner', 'intermediate', 'advanced'))
);

注：

• IDENTITY(1,1) 表示自动编号的标识列。
• CHECK 约束用于限制列的取值范围。
• REFERENCES 用于设置外键。
• UNIQUE 用于设置唯一约束。