39-建表约束与数据类型选型

Oracle 建表约束与数据类型选型

文章目录

• Oracle 建表约束与数据类型选型
• • 前言
  • 一、CREATE TABLE 全语法
  • 二、五种约束逐个说
  • • 主键约束
    • 唯一约束
    • CHECK 约束
    • NOT NULL 约束
    • 外键约束
  • 三、ON DELETE CASCADE 与 SET NULL
  • 四、延迟约束 DEFERRABLE
  • 五、VARCHAR2 的 BYTE 与 CHAR 语义
  • 六、NUMBER(p, s) 的门道
  • 七、DATE、TIMESTAMP 与 CLOB
  • 八、DEFAULT 与虚拟列
  • 九、ALTER 改列的注意事项
  • 写在最后

前言

增删改查会用了，接下来该自己建表了。这篇覆盖 CREATE TABLE 全家桶：五种约束怎么写怎么命名，外键的删除级联，延迟约束的小众妙用，数据类型怎么选才不给后人埋雷，以及 ALTER 改列时那些必须知道的红线。

一、CREATE TABLE 全语法

先建一张相对完整的表，把常见元素都放进去：

CREATE TABLE t_employee (
emp_id NUMBER(10),
emp_name VARCHAR2(50 CHAR) NOT NULL,
id_card VARCHAR2(18),
gender CHAR(1) DEFAULT 'M',
salary NUMBER(10, 2),
bonus NUMBER(10, 2) DEFAULT 0,
yearly_pay AS (salary * 12 + NVL(bonus, 0)),
hire_date DATE DEFAULT SYSDATE,
dept_no NUMBER(4),
CONSTRAINT pk_employee PRIMARY KEY (emp_id),
CONSTRAINT uk_employee_idcard UNIQUE (id_card),
CONSTRAINT ck_employee_gender CHECK (gender IN ('M', 'F')),
CONSTRAINT ck_employee_salary CHECK (salary > 0),
CONSTRAINT fk_employee_dept FOREIGN KEY (dept_no)
REFERENCES t_dept (dept_no)
);

表级约束统一写在列定义之后，配合 CONSTRAINT 关键字显式命名。命名的意义在报错时体现：违反约束时 Oracle 报的是约束名，SYS_C005421 这种系统自动名基本没法排查，规范命名一看就懂。习惯格式：类型缩写加表名加列名，主键 PK_、唯一 UK_、检查 CK_、外键 FK_。

二、五种约束逐个说

主键约束

主键 = 唯一 + 非空，一张表最多一个：

— 建表时列级写法
emp_id NUMBER(10) CONSTRAINT pk_employee PRIMARY KEY

— 事后补加
ALTER TABLE t_employee ADD CONSTRAINT pk_employee PRIMARY KEY (emp_id);

组合主键必须写表级形式：PRIMARY KEY (col1, col2)。加主键时 Oracle 会自动建唯一索引，主键列上天然有索引支撑。

唯一约束

CONSTRAINT uk_employee_idcard UNIQUE (id_card)

唯一约束允许 NULL，而且多个 NULL 不冲突（NULL 不参与唯一性判断），这是和主键的关键区别。唯一约束同样靠唯一索引实现。唯一索引和唯一约束不等价：约束是逻辑规则可被禁用启用，索引只是物理结构。

CHECK 约束

CONSTRAINT ck_employee_salary CHECK (salary > 0 AND salary < 1000000)

CHECK 里不能嵌套子查询、不能用 SYSDATE 这类会变的函数，只能引用本行的列。想让"工资必须大于本部门平均"这种规则生效，得靠触发器或应用层校验。另外 gender IN ('M', 'F') 这类枚举约束，11g 后也可以考虑建虚拟列加外键到字典表，纯 CHECK 更简单够用。

NOT NULL 约束

emp_name VARCHAR2(50 CHAR) NOT NULL

NOT NULL 只能列级定义，本质是一个预置的 CHECK 约束。事后加用 MODIFY：

ALTER TABLE t_employee MODIFY (emp_name VARCHAR2(50 CHAR) NOT NULL);

注意如果表里已有 NULL 数据，这一步会报 ORA-02296，先把存量 NULL 补值再改。

外键约束

CONSTRAINT fk_employee_dept FOREIGN KEY (dept_no)
REFERENCES t_dept (dept_no);

外键保证引用完整性：子表插入的 dept_no 必须在父表存在；默认情况下父表被引用的行不许删除，删了报 ORA-02292。

三、ON DELETE CASCADE 与 SET NULL

删除父表行时对子表的处理策略，三种对比：

— 默认：禁止删除被引用的父行，报 ORA-02292
CONSTRAINT fk_employee_dept FOREIGN KEY (dept_no)
REFERENCES t_dept (dept_no);

— 级联删除：删部门，员工跟着没
CONSTRAINT fk_employee_dept FOREIGN KEY (dept_no)
REFERENCES t_dept (dept_no) ON DELETE CASCADE;

— 置空：删部门，员工的 dept_no 变 NULL，人还在
CONSTRAINT fk_employee_dept FOREIGN KEY (dept_no)
REFERENCES t_dept (dept_no) ON DELETE SET NULL;

选型建议：财务、订单这类要留痕的数据慎用 CASCADE，一次 DELETE 可能级联抹掉几千行子表数据；SET NULL 更温和，适合"关系断了但记录留着"的语义。无论哪种，外键列必须有索引，否则删父表行时子表全表扫描加锁，高并发下就是锁等待现场。

四、延迟约束 DEFERRABLE

普通约束在语句执行瞬间检查，违反立刻报错。DEFERRABLE 约束可以把检查推迟到 COMMIT 时：

CREATE TABLE t_finance (
id NUMBER(10) PRIMARY KEY,
amount NUMBER(10, 2) CHECK (amount > 0) DEFERRABLE INITIALLY DEFERRED
);

INITIALLY DEFERRED 表示默认就延迟到提交才查；DEFERRABLE INITIALLY IMMEDIATE 则默认立即查，需要时再切换：

SET CONSTRAINT ck_finance_amount DEFERRED;

典型场景是批量换序操作：两行数据要互换主键，立即检查的约束中间状态必然违反约束，延迟约束让整批操作完成后统一校验。另一个场景是外键互相引用的两张表循环插入。

查看约束是否可延迟：

SELECT constraint_name, deferrable, deferred
FROM user_constraints
WHERE table_name = 'T_FINANCE';

五、VARCHAR2 的 BYTE 与 CHAR 语义

VARCHAR2(50) 里的 50 默认指字节还是字符，由参数 NLS_LENGTH_SEMANTICS 决定，默认 BYTE。中文在常见 UTF-8 库里一个字占 3 字节，VARCHAR2(10) 只能存 3 个汉字多一点：

SELECT * FROM nls_database_parameters
WHERE parameter = 'NLS_LENGTH_SEMANTICS';

— 显式声明字符语义，与库参数解耦
emp_name VARCHAR2(50 CHAR)

建议建表时对可能存中文的列显式写 CHAR，别赌库参数。尝试塞超长字符串报 ORA-12899。顺便说 CHAR 类型是定长、不足补空格，比较时容易踩空格坑，业务表几乎不用它，统一 VARCHAR2。

六、NUMBER(p, s) 的门道

NUMBER 是 Oracle 的全能数字类型，p 是有效数字总位数（1 到 38），s 是小数位数：

salary NUMBER(10, 2) — 最多 8 位整数加 2 位小数，存 12345678.99 没问题
rate NUMBER(4, 3) — 0.123 这种，整数位只留 1 位

几个变体：

• NUMBER 不带参数：完全浮点语义，能存多大存多大
• NUMBER§：整数，等价 NUMBER(p, 0)
• INTEGER 是 NUMBER(38) 的别名
• 存金额用 NUMBER(10,2) 这类定点，别用 BINARY_FLOAT/DOUBLE，二进制浮点存 0.1 会有精度误差

s 为负数的冷门用法：NUMBER(5, -2) 表示小数点左边两位截掉，存的是整百数。一般用不上，看懂别人代码即可。

七、DATE、TIMESTAMP 与 CLOB

DATE 含日期和时间，精确到秒：

ALTER TABLE t_employee ADD (quit_time DATE);
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

TIMESTAMP 精确到小数秒，还能带时区：

ALTER TABLE t_employee ADD (last_login TIMESTAMP(6),
log_tz TIMESTAMP(6) WITH TIME ZONE);
SELECT SYSTIMESTAMP FROM dual;

带 LOCAL TIME ZONE 的版本会按会话时区转换显示。记时间差的教训：DATE 相减得到的是"天"为单位的数，算分钟要乘 1440；两个 TIMESTAMP 相减直接得到 INTERVAL 天时分秒，好读但比较时麻烦。

CLOB 存大文本（备注、JSON、日志）：

ALTER TABLE t_employee ADD (remark CLOB);

CLOB 的坑：作为查询条件、建索引、GROUP BY 都受限，普通列能干的它不能全干。长度两千字节以内的文本老版本常用 VARCHAR2(4000)，12c 起可以开 32767 扩展。JSON 文档 12c 后可以用 JSON_VALUE 查询，但那是另一个话题了。

八、DEFAULT 与虚拟列

DEFAULT 在 INSERT 缺省时兜底：

hire_date DATE DEFAULT SYSDATE
— 12c 起，默认值还可以用序列
emp_id NUMBER(10) DEFAULT seq_employee.NEXTVAL

虚拟列（11g 起）由表达式实时算出，不占存储、不能 INSERT：

yearly_pay AS (salary * 12 + NVL(bonus, 0))

虚拟列上可以建索引、加约束，拿它当生成列用很顺手：

CREATE INDEX idx_employee_yearly ON t_employee (yearly_pay);
INSERT INTO t_employee (emp_id, emp_name, salary, bonus)
VALUES (1, 'TEST', 5000, 1000); — 不给 yearly_pay 赋值，自动算出 61000

九、ALTER 改列的注意事项

常见 ALTER 操作与红线：

— 加列，安全
ALTER TABLE t_employee ADD (email VARCHAR2(100));

— 删列，数据直接没了，慎重
ALTER TABLE t_employee DROP COLUMN email;

— 改列名
ALTER TABLE t_employee RENAME COLUMN email TO mail_addr;

— 改类型或改大长度
ALTER TABLE t_employee MODIFY (mail_addr VARCHAR2(200));

红线一：列里有数据时，VARCHAR2 只能改大不能改小，改小报 ORA-01441；想把 NUMBER 改成 VARCHAR2 这类跨类型，列必须全空，否则报 ORA-01439。

红线二：大表 DROP COLUMN 是逐行清数据，锁表时间长，用 SET UNUSED 先逻辑删除再闲时清理：

ALTER TABLE t_employee SET UNUSED (old_col);
ALTER TABLE t_employee DROP UNUSED COLUMNS;

红线三：改带默认值的加列操作，11g 前会重写全表每一行，12c 后只存元数据，秒级完成，版本差异要心里有数。

写在最后

表设计三件事：类型选对（CHAR 别用、VARCHAR2 显式 CHAR、金额定点 NUMBER）、约束命好名（PK_/UK_/CK_/FK_ 前缀）、外键想清楚删除语义（CASCADE 慎用）。下一篇把视图、序列、同义词、DBLink 四个常用对象一网打尽。