Oracle Live SQL中执行表查询无结果及报错问题咨询

先来看你遇到的两个核心报错：ORA-02268: referenced table does not have a primary key 和 ORA-00942: table or view does not exist，我来逐个拆解原因和修复方案：

一、报错原因拆解 #

1. ORA-02268: referenced table does not have a primary key #

你创建employees表时，给department_id字段加了外键约束employees_department_id_fk references departments，但**departments表根本没定义主键**！Oracle要求外键引用的目标表必须有主键（或者唯一约束），否则无法建立合法的外键关联，这就是第一个报错的根源。

更关键的是：你后续插入employees数据时用的department_id是1，但departments表连department_id这个字段都没有，插入逻辑完全不成立——你想关联的字段在父表中根本不存在！

2. ORA-00942: table or view does not exist #

这个报错是连锁反应：因为创建employees表的语句因外键问题执行失败，所以employees表根本没被成功创建。后面插入员工数据、执行关联查询时，自然找不到这个不存在的表，就抛出了这个错误。

二、完整修复方案 #

我们需要给departments表添加主键字段，让employees的外键正确关联这个主键，再重新执行所有语句。修正后的完整代码如下：

-- 创建带自增主键的departments表
create table departments ( 
    department_id number generated always as identity primary key, -- 让Oracle自动生成主键值
    name varchar2(255) not null, 
    location varchar2(4000), 
    country varchar2(4000) 
) ;

-- 创建employees表，外键关联departments的主键字段
create table employees ( 
    employee_id number generated always as identity primary key, -- 可选：给员工表也添加主键
    department_id number constraint employees_department_id_fk references departments(department_id) on delete cascade, 
    name varchar2(50) not null, 
    email varchar2(255), 
    cost_center number, 
    date_hired date, 
    job varchar2(255) 
) ;

-- 插入部门数据，无需手动指定department_id，由Oracle自动生成
insert into departments ( name, location, country ) values ( 'Security', 'Tanquecitos', 'United States' );
insert into departments ( name, location, country ) values ( 'Travel', 'Sugarloaf', 'United States' );
insert into departments ( name, location, country ) values ( 'Office of the CEO', 'Dale City', 'United States' );
insert into departments ( name, location, country ) values ( 'Security', 'Grosvenor', 'United States' );
commit;

-- 插入员工数据，department_id对应自动生成的部门主键（1-4）
insert into employees ( department_id, name, email, cost_center, date_hired, job ) values ( 1, 'Gricelda Luebbers', 'gricelda.luebbers@aaab.com', 20, sysdate - 94, 'Systems Designer' );
insert into employees ( department_id, name, email, cost_center, date_hired, job ) values ( 1, 'Dean Bollich', 'dean.bollich@aaac.com', 11, sysdate - 74, 'Legal Assistant' );
insert into employees ( department_id, name, email, cost_center, date_hired, job ) values ( 1, 'Milo Manoni', 'milo.manoni@aaad.com', 21, sysdate - 68, 'Systems Designer' );
insert into employees ( department_id, name, email, cost_center, date_hired, job ) values ( 1, 'Laurice Karl', 'laurice.karl@aaae.com', 78, sysdate - 73, 'Programmer' );
commit;

-- 用标准JOIN语法执行关联查询，比旧写法更清晰易读
select 
    d.name department_name, 
    d.location location, 
    d.country country, 
    e.name employee_name, 
    e.email email, 
    e.cost_center cost_center, 
    e.date_hired date_hired, 
    e.job job 
from departments d
join employees e on d.department_id = e.department_id;

关键修复点说明： #

• 给departments表新增了department_id主键，用generated always as identity实现主键自增，避免手动维护ID的麻烦
• employees表的外键明确关联departments(department_id)，完全符合Oracle外键约束的要求
• 查询语句改用标准JOIN语法，比逗号分隔表的旧写法更清晰，也更便于后续维护
• 所有数据插入后都执行commit，确保数据持久化到数据库

三、执行后预期效果 #

执行修正后的代码后，你会看到：

1. 两张表成功创建，无约束报错
2. 部门和员工数据都能正常插入
3. 关联查询会返回正确的部门-员工关联数据

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