Oracle dead with unknown reason

These commands should be run from the OS command line not the SQL prompt.

Mostly just for my own amusement, you can do this with a materialized view which has a unique index:

CREATE MATERIALIZED VIEW LOG ON COMPANY
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON DEPARTMENT
WITH PRIMARY KEY, ROWID (company_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE
WITH PRIMARY KEY, ROWID (department_id, username)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW xcheck_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT c.id as company_id, lower(e.username) as username,
    c.rowid as c_rowid, d.rowid as d_rowid, e.rowid as e_rowid
from company c, department d, employee e
where d.company_id = c.id
and e.department_id = d.id;

CREATE UNIQUE INDEX xcheck_ind ON xcheck_mv(company_id, username);

Then attempting to insert an employee record for a different department with the same username gives a unique constraint violation, although not until you commit:

insert into company (id) values(1);

1 row created.

SQL> insert into department (id, company_id) values(1, 1);

1 row created.

SQL> insert into department (id, company_id) values(2, 1);

1 row created.

SQL> insert into employee (id, department_id, username) values(1,1,'Joe Bloggs');

1 row created.

SQL> insert into employee (id, department_id, username) values(2,2,'Joe Bloggs');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (STACKOVERFLOW.XCHECK_IND) violated

Just for fun I put lower() in the MV definition to catch the most basic workaround - so trying to insert 'joe bloggs' also fails - but this model is never going to be very robust.

I'm not saying this is a good idea, just that it's possible...