Hi
I am trying to create a composite unique index on a table. This table represents codes used by teams in an organisation. The codes will be defined at organisation level and teams can add additional codes.
When a code exists for the organisation, it should not exist at team level. However, different teams can have the same additional codes at team level.
I think therefore I need to create a conditional unique index, possibly by using a function based index, but I'm wondering if it's too complicated and I should use a trigger instead?
Below is a simple illustration of the situation, which is a very rough and clearly omits other table structures, key constraints etc. I hope this helps, please let me know if I can provide more information. We're running Oracle 12.1
create table code (
code_id NUMBER not null,
code VARCHAR2(30) not null,
organisation VARCHAR2(30) not null,
team VARCHAR2(30)
)
--This index prevents duplication of codes at organisation level
create unique index code_unique on code ( code asc, organisation asc )
| code_id | code | organisation
| team |
|---|
| 1 | annual_leave | org_A | null |
| 2 | maternity_leave | org_A | null |
| 3 | sick_leave | org_A | null |
| 4 | study_leave | org_A | team_1 |
| 5 | compassionate_leave | org_A | team_2 |
So in the example above, the first 3 rows would apply to all teams within the organisation, however rows 4 and 5 would only apply to the two individual teams.. The following inserts (in blue) should be permitted
| code_id | code | organisation
| team |
|---|
| 1 | annual_leave | org_A | null |
| 2 | maternity_leave | org_A | null |
| 3 | sick_leave | org_A | null |
| 4 | study_leave | org_A | team_1 |
| 5 | compassionate_leave | org_A | team_2 |
| 6 | compassionate_leave | org_A | team_1 |
| 7 | annual_leave | org_B | null |
I want to prevent a team inserting a code that already exists at organisation level i.e in red below
| code_id | code | organisation
| team |
|---|
| 1 | annual_leave | org_A | null |
| 2 | maternity_leave | org_A | null |
| 3 | sick_leave | org_A | null |
| 4 | study_leave | org_A | team_1 |
| 5 | compassionate_leave | org_A | team_2 |
| 6 | compassionate_leave | org_A | team_1 |
| 7 | annual_leave | org_B | null |
| 8 | annual_leave | org_B | team_1 |
I think it should look something like this:
if (team is null) then
I need the following index
create unique index code_unique on code ( code asc, organisation asc )
else
I need the following index to prevent duplication in the same team:
create unique index code_unique on code ( code asc, organisation asc, team asc )
BUT I also need to check at organisation level at the same time and this is where I get stuck.
Thank you for your help.
Annelize