无法添加外键约束：HS_INVOICE表需先创建主键吗？

问题背景 #

遇到「无法添加外键约束」的错误，原因涉及的一张表未设置主键约束：

• INVOICE与HS_INVOICE两表均包含主键字段ID_INVC
• INVOICE表已配置主键约束PK_INVOICE，且已成功添加外键约束FK_INVOICE_3（脚本见下文）
• HS_INVOICE表的ID_INVC字段未配置主键约束

INVOICE表添加外键的脚本：

ALTER TABLE INVOICE
ADD (
ID_ALS_ADMNSTRTR NUMBER(10,0) NULL );

COMMENT ON COLUMN INVOICE.ID_ALS_ADMNSTRTR      IS      'Primary key for the ALS Administrator that is assigned to the invoice';

Alter TABLE INVOICE
ADD CONSTRAINT "FK_INVOICE_3" FOREIGN KEY ("ID_ALS_ADMNSTRTR")
 REFERENCES "CCL"."ALS_ADMINISTRATOR" ("ID_ALS_ADMNSTRTR")ENABLE;

INVOICE表已存在的主键约束：

CONSTRAINT "PK_INVOICE" PRIMARY KEY ("ID_INVC")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA01"  ENABLE

问题解答 #

1. 是否需要先为HS_INVOICE表创建主键约束，再添加外键约束？ #

是的，必须先为HS_INVOICE表的ID_INVC字段创建主键约束：

• 若其他表要将HS_INVOICE的ID_INVC作为外键引用，数据库要求被引用字段必须是主键约束或唯一约束，主键约束是最合规的标准配置
• 即使是HS_INVOICE自身要添加其他外键约束，也需先配置自身主键约束——这是数据表设计的基础规范，保证表数据的唯一性与完整性

2. HS_INVOICE表的约束添加脚本 #

第一步：创建主键约束

参考INVOICE表的主键配置，为HS_INVOICE添加主键约束：

ALTER TABLE HS_INVOICE
ADD CONSTRAINT "PK_HS_INVOICE" PRIMARY KEY ("ID_INVC")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA01" ENABLE;

第二步：添加外键约束（以引用ALS_ADMINISTRATOR为例）

如果需要为HS_INVOICE添加类似INVOICE的外键约束（关联ALS_ADMINISTRATOR表），脚本如下：

-- 若HS_INVOICE无该字段，先添加外键字段
ALTER TABLE HS_INVOICE
ADD (ID_ALS_ADMNSTRTR NUMBER(10,0) NULL);

-- 添加字段注释
COMMENT ON COLUMN HS_INVOICE.ID_ALS_ADMNSTRTR IS 'Primary key for the ALS Administrator that is assigned to the HS invoice';

-- 添加外键约束
ALTER TABLE HS_INVOICE
ADD CONSTRAINT "FK_HS_INVOICE_3" FOREIGN KEY ("ID_ALS_ADMNSTRTR")
 REFERENCES "CCL"."ALS_ADMINISTRATOR" ("ID_ALS_ADMNSTRTR") ENABLE;

如果是其他表要引用HS_INVOICE的ID_INVC作为外键，示例脚本（以OTHER_TABLE为例）：

ALTER TABLE OTHER_TABLE
ADD CONSTRAINT "FK_OTHER_TABLE_HS_INVOICE" FOREIGN KEY ("ID_INVC")
 REFERENCES "HS_INVOICE" ("ID_INVC") ENABLE;

内容的提问来源于stack exchange，提问作者AlgoDSExpert