Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite
1

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.

share|improve this question
Perhap a unique function based index could be used. Here is an example: stackoverflow.com/questions/6702367/… – Brian Jan 27 '12 at 2:07
Thanks for suggestion, but as far as I remember, function has to be deterministic – a1ex07 Jan 27 '12 at 4:05
3  
I would go for 2.Just adding company_id to EMPLOYEE. Add company_id and create composite unique key for username,company_id. Why do you think it it is not ideal? – Atilla Ozgur Jan 27 '12 at 11:35
I think it adds extra path from user to company, so I have to ensure that company in users is the same as in departments… I believe the right way involves redesigning schema (and implementing of Party-Role-Relationship), but I cannot do that at the moment, so I 'm trying to find alternatives which require minimal changes to the model. – a1ex07 Jan 27 '12 at 14:06
1  
i think in an entity relationship model departement is a "weak entity", the primary key of the departement is the a pair consisting of a company_id and another id-column disinguishing the different departements of a company. This pair is contained in the employee table. I think uniqueness constraint shall be implemented by unqiue keys therefore you need a table containing the company key and the employee name – miracle173 Jan 29 '12 at 16:40
show 5 more comments

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
Thank you very much for this idea. I'll definitely give a try. – a1ex07 Jan 30 '12 at 16:38
up vote 1 down vote

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),...);
share|improve this answer
Thanks for the answer; I know it will work, but I inclined to Alex Poole solution with materialized view. I realized that in addition to solving uniqueness problem , after some modifications it will also give me an option to query the view (at least for my typical queries), so it will serve 2 purposes... – a1ex07 Jan 31 '12 at 0:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.