Implementing uniqueness with Oracle

Question

For example, say I have something like

CREATE TABLE COMPANY(id int not null primary key, ...);

CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null,
 CONSTRAINT FK_DEP_COMPANY_ID FOREIGN KEY(company_id) REFERENCES COMPANY(id),...);

CREATE TABLE EMPLOYEE(id int not null primary key, department_id int not null, 
  username varchar(30) NOT NULL, ...,
 CONSTRAINT FK_EMPLOYEE_DEP_ID FOREIGN KEY(department_id) REFERENCES DEPARTMENT(id));

I have to implement uniqueness of EMPLOYEE.username within one company.

I see 2 ways.
1. Using AFTER statement level trigger on EMPLOYEE table (something similar to http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936 , case1)
2. Just adding company_id to EMPLOYEE

Neither of them looks ideal to me, and I wonder what is the proper way to implement uniqueness in such case.

Thanks.

Answer 1

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...

Answer 2

If you decide on Option 2, the Foreign Key Constraint should be changed. too:

CREATE TABLE EMPLOYEE(id int not null primary key, department_id int not null, 
  company_id not null,
  username varchar(30) NOT NULL, ...,
 CONSTRAINT FK_EMPLOYEE_DEP_ID FOREIGN KEY(department_id, company_id) 
     REFERENCES DEPARTMENT(id, company_id));

The department table may also need a Unique Key on (id, company_id) or changing the Primary Key:

CREATE TABLE DEPARTMENT(id int not null, company_id int not null,
 PRIMARY KEY (id, company_id),
 CONSTRAINT FK_DEP_COMPANY_ID FOREIGN KEY(company_id) REFERENCES COMPANY(id),...);