【Oracle专栏】五类约束命名 && 实践建议

Oracle && OceanBase 相关文档，希望互相学习，共同进步

风123456789～-CSDN博客

1.背景

        最近对比两个不同环境的库时，发现多出来很多SYS_C00107980 的约束。尤其是生产数据库，命名没有按照规范约定走。

        本文主要针对 oracle  约束的创建、命名、默认名称修改，利弊分析 及相关知识点整理。

2.实验- 创建及修改

2.1 整体说明

      Oracle约束是数据库层面保障数据完整性的核心机制，本质是依附于表的元数据声明，部分约束会自动关联物理索引，所有约束规则在DML执行瞬间由数据库内核强制拦截，比应用层校验更难绕过、更可靠。

      五大核心约束类型：  ‌    1）主键约束（P）‌：非空+唯一双重保障，自动创建唯一索引，用于唯一标识表中每一行数据，不允许出现空值和重复值。      2）‌外键约束（R）‌：保障关联表之间的引用完整性，子表字段值必须在父表对应字段中存在，外键列默认允许为空，不会自动创建索引，建议手动建索引避免锁等待。     3）‌唯一约束（U）‌：保障字段值全局唯一，自动创建唯一索引，单列唯一约束允许存在任意多个NULL值。    4）‌非空约束（C）‌：强制指定字段不能插入空值，不会自动创建索引，属于CHECK约束的特殊子类。  ‌  5）检查约束（C）‌：自定义数据校验规则，比如限制字段取值范围、格式等，插入/更新数据时触发规则校验。

–创建表
create table aaa
(kid number,
typ varchar2(2),
nam varchar2(50),
bankkid varchar2(50)
);

create table bbb
(kid varchar2(50),
banknam varchar2(50),
banktyp varchar2(50)
)

    以下主要针对 主键约束、外键约束、唯一约束，以及非空约束、检查约束 进行实验增、改。

位置：

       其中主键索引可以不输入名称，使用系统生成的默认名称（如 SYS_C0012345）。其他必须有名称才可以保存。

2.2 主键约束 P 

Keys: Primary     （主键约束可以在创建表 或之后增加，语法一样）

1）增加

–不带名称添加 主键
alter table aaa add primary key(kid);

2）查询约束

–查看创建的主键名称
SELECT A.constraint_name, — 主键约束名
A.status, — 状态 (ENABLED/DISABLED)
B.column_name, — 列名
B.position — 列顺序
FROM user_constraints A
JOIN user_cons_columns B
ON A.constraint_name = B.constraint_name
WHERE A.table_name = 'AAA'
AND A.constraint_type = 'P';

截图： 创建后默认系统生成的默认名称

此时，会自动生成一个同名的唯一索引。

同名的唯一索引不能单独删除，同pk同生命周期。

3）修改

修改约束名称  或者重建主键：

–修改约束名称,同时索引的也要修改 如果不修改索引的，删除主键时也会删除索引的
alter index SYS_C00146357 rename to pk_aaa;

alter table AAA
rename constraint SYS_C00146357 to pk_aaa;
–删除主键约束
alter table AAA
  drop constraint SYS_C00146357 cascade;

–添加待名称的主键约束
alter table AAA
add constraint PK_AAA primary key (KID)

2.3 唯一约束 U

Keys: Unique

1）增加

      增加唯一约束时，会自动增加一个唯一索引（同主键约束，只有它俩）。除此外，可以再增加其他的唯一约束; 也可以多列唯一约束。

–增加唯一约束
alter table AAA
add constraint UK_AAA unique (TYP);

注意：增加唯一约束时，不可以不填写约束名称，否则会报错 （不同于主键约束）

2）修改

修改约束名称：   同主键

–修改约束名称 同主键约束语法
alter index A1 rename to uk_aaa;

alter table AAA
rename constraint A1 to uk_aaa;

3）删除

删除约束名称：  同主键

alter table AAA
drop constraint UK_AAA cascade;

2.4 外键约束 F

Keys: Foreign

1）添加

–增加外键约束
alter table AAA
add constraint fk_aaa foreign key (BANKKID)
references bbb (KID);

        以上会报错，因为bbb表没有唯一约束，增加如下。

alter table BBB
add constraint PK_BBB primary key (KID)

       再次执行外键约束增加语句，ok:

       在 Oracle 数据库中，外键（Foreign Key）引用的目标列‌不必必须是主键（Primary Key）‌，但它‌必须具有唯一性约束‌。

外键可以引用另一个表的以下两种约束之一：

        外键的作用是维护‌参照完整性‌（Referential Integrity），即子表中的每一行数据必须在父表中找到‌唯一确定‌的对应记录。       如果父表的列允许重复值，子表的一条记录就无法明确指向父表的哪一条具体记录，从而破坏数据一致性逻辑。因此，Oracle 强制要求外键引用的列必须拥有 ‌PRIMARY KEY‌ 或 ‌UNIQUE‌ 约束。

     也可以在创建表时，用列级或表级约束创建外键索引：

–列级约束（Column Level）
CREATE TABLE ccc (
kid number primary key,
typ varchar2(2),
nam varchar2(50),
bankkid varchar2(50) CONSTRAINT fk_ccc REFERENCES bbb(kid)
);

–表级约束（Table Level）
CREATE TABLE ddd (
kid number primary key,
typ varchar2(2),
nam varchar2(50),
bankkid varchar2(50),
CONSTRAINT fk_ddd FOREIGN KEY (bankkid) REFERENCES bbb(kid)
);

2）修改、删除

修改外键索引、删除外键索引（同其他）

–重命名 （同其他）
alter table DDD
rename constraint FK_DDD to FK_DDD1;

–删除 （同其他）
alter table DDD
drop constraint FK_DDD;

2.5 非空约束 C

Columns ：列上直接增加该属性即可

1）定义

• ‌非空约束 (NOT NULL Constraint)‌：
  • ‌性质‌：逻辑规则。
  • ‌作用‌：强制列中不能包含 NULL 值。
  • ‌实现‌：Oracle 在数据字典中记录该列属性来实施，‌默认情况下不会自动创建索引‌。

2）添加

–创建时指定
CREATE TABLE eee (
kid number,
typ varchar2(2),
nam varchar2(50),
bankkid varchar2(50) not null
);
–修改为非空约束
ALTER TABLE aaa MODIFY typ varchar2(10) NOT NULL;

ALTER TABLE aaa MODIFY nam NOT NULL;

3）修改

修改非空 为可空时：

alter table EEE modify bankkid null;

2.6 检查约束 C

Checks: 增加相关的条件

1）定义

        检查约束（CHECK Constraint）‌ 是 Oracle 数据库中用于保证数据完整性和准确性的关键机制。它通过定义一个逻辑表达式（布尔条件），强制表中每一行数据在插入或更新时必须满足该条件。如果操作导致表达式结果为 FALSE，数据库将拒绝执行并报错；如果结果为 TRUE 或 UNKNOWN（通常涉及 NULL 值），则操作允许通过。

‌      域完整性验证‌：限制列值的范围、格式或特定集合。例如：年龄必须大于 0，性别只能是 'M' 或 'F'。  ‌     业务规则落地‌：将业务逻辑直接固化在数据库层，防止因应用层漏洞或直连数据库操作导致的“脏数据”。  ‌    多列关联校验‌：可以比较同一行中不同列的值。例如：结束日期必须晚于开始日期 (end_date > start_date)

2）创建

CHECK 约束可以在创建表时定义（列级或表级），也可以在表创建后通过 ALTER TABLE 添加。

–列级
CREATE TABLE fff (
emp_id NUMBER PRIMARY KEY,
salary NUMBER(10, 2) CONSTRAINT chk_sal_positive CHECK (salary > 0),
gender CHAR(1) CONSTRAINT chk_gender_valid CHECK (gender IN ('M', 'F'))
);
–表级
CREATE TABLE ggg (
proj_id NUMBER PRIMARY KEY,
start_date DATE,
end_date DATE,
CONSTRAINT chk_date_order CHECK (end_date >= start_date)
);

–如果表已存在且数据符合新规则，修改添加
ALTER TABLE fff
ADD CONSTRAINT chk_salary_range
CHECK (salary >= 200 AND salary <= 100000);

3）注意

注意：

2.7.关键事实补充

      1）主键约束‌必须‌有唯一索引支撑；若未指定索引名，Oracle 会‌自动创建同名索引‌（约束名 = 索引名）。       2）删除主键约束时，其关联索引同步删除；若索引是手动创建且名称不同，则需单独处理。       3）默认名称长度受限于 Oracle 对象名上限（30 字符，12c+ 可更长），但 SYS_Cxxxxx 格式本身无业务含义。

       在 Oracle 中执行 DROP TABLE 时，‌该表上所有依赖对象（包括索引、约束、触发器）均会被自动移除‌，无需额外指定。

• 索引‌：所有基于该表的索引（含主键/唯一约束自动创建的索引）随表一同删除。
• ‌约束‌：该表自身定义的所有约束（主键、外键、唯一、检查等）一并删除；若其他表存在‌外键引用此表‌，默认会报错阻止删除，需加 CASCADE CONSTRAINTS 选项强制级联删除引用约束。
• ‌其他对象‌：
  • 视图、同义词：定义保留但状态变为 INVALID（失效），需手动重建或重新编译。
  • 触发器：随表删除（非“不触发”，而是对象本身被清除）。
• ‌回收站机制‌（Oracle 10g+）：
  • 默认 DROP TABLE 表名 → 表及关联对象进入回收站（可 FLASHBACK 恢复，恢复后索引/约束名称可能由系统重命名）。

主键或唯一约束自动创建索引：       当定义 PRIMARY KEY 或 UNIQUE 约束时，Oracle 会‌自动创建一个唯一索引‌来支撑该约束。       PRIMARY KEY = NOT NULL + UNIQUE + ‌自动创建唯一索引‌。       UNIQUE = UNIQUE (允许NULL) + ‌自动创建唯一索引‌。 ‌      注意‌：单纯的 NOT NULL 约束‌不会‌触发自动索引创建。

3.利弊总结 |实践建议 

3.1 说明

      Oracle 允许主键约束及关联索引使用系统生成的默认名称（如 SYS_C0012345），技术上无功能障碍，但‌强烈不建议在生产环境长期采用‌。

3.2 利弊分析

3.3.实践建议

‌    1）开发/测试环境‌：可临时用默认名加速原型验证。 ‌    2）生产环境‌：‌必须强制自定义命名‌，遵循团队规范（例：主键 PK_<TABLE>，唯一索引 UK_<TABLE>_<COLS>，普通索引 IX_<TABLE>_<COLS>）。     3）若已存在大量默认名对象，建议通过脚本批量重命名（需停机窗口 + 充分测试），避免遗留技术债。

注：Oracle 官方文档虽未禁止默认名，但所有企业级开发规范（如阿里、华为、Oracle 自身最佳实践）均明确要求语义化命名，以提升可维护性与可观测性。

到这里吧：ok 

项目管理–相关知识   

项目管理-项目绩效域1/2-CSDN博客

项目管理-项目绩效域1/2_八大绩效域和十大管理有什么联系-CSDN博客

项目管理-项目绩效域2/2_绩效域 团不策划-CSDN博客

高项-案例分析万能答案（作业分享）-CSDN博客

项目管理-计算题公式【复习】_项目管理进度计算题公式:乐观-CSDN博客

项目管理-配置管理与变更-CSDN博客

项目管理-项目管理科学基础-CSDN博客

项目管理-高级项目管理-CSDN博客

项目管理-相关知识（组织通用治理、组织通用管理、法律法规与标准规范）-CSDN博客

Oracle其他文档，希望互相学习，共同进步

Oracle-找回误删的表数据(LogMiner 挖掘日志)_oracle日志挖掘恢复数据-CSDN博客

oracle 跟踪文件–审计日志_oracle审计日志-CSDN博客

ORA-12899报错，遇到数据表某字段长度奇怪现象：“Oracle字符型，长度50”但length查却没有50_varchar(50) oracle 超出截断-CSDN博客

EXP-00091: Exporting questionable statistics.解决方案-CSDN博客

Oracle 更换监听端口-CSDN博客