I have this table that stores historical data of a record. Any update even with code and name fields should insert a new record of the same id but may have different values for code, name and effective start date.

create table jobs (
    id              number generated by default as identity
  , code            varchar2(030) not null
  , name            varchar2(100) not null
  -- 
  , effective_start_date date   not null
  , effective_end_date   date
  --
  , created_on      date    not null
  , created_by      number  not null
  , last_updated_on date    not null
  , last_updated_by number  not null
);

Below are sample records with some that I want to get through and some should not.

Basically, a new record with new ID that uses a code and a name that already exists in another record with different ID should not be allowed.

How do I define how to define the correct constraints or indexes to achieve this?

Appreciate any help.

I have this table that stores historical data of a record. Any update even with code and name fields should insert a new record of the same id but may have different values for code, name and effective start date.

create table jobs (
    id              number generated by default as identity
  , code            varchar2(030) not null
  , name            varchar2(100) not null
  -- 
  , effective_start_date date   not null
  , effective_end_date   date
  --
  , created_on      date    not null
  , created_by      number  not null
  , last_updated_on date    not null
  , last_updated_by number  not null
);

Below are sample records with some that I want to get through and some should not.

Basically, a new record with new ID that uses a code and a name that already exists in another record with different ID should not be allowed.

How do I define how to define the correct constraints or indexes to achieve this?

Appreciate any help.

• oracle-database
• indexing
• constraints

• You really don't want to do that. Have a single key per entity, not three keys. This problem is a clear indication that you have a normalization problem and need to rethink your table design. You can certainly achieve the constraints you want with a fancy use of triggers, but that's a real headache to manage. You'd be far better off normalizing your model. – Paul W Commented Mar 28 at 14:55
• I'm re-thinking my design now. Thanks for the tip. – ads Commented Mar 28 at 23:29

1 Answer 1

Without pretending that you should not improve your model, you can try this tricky solution:

CREATE  MATERIALIZED VIEW LOG ON jobs WITH rowid(id, code) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_jobs 
REFRESH FAST ON COMMIT 
AS 
SELECT j1.code, COUNT(*) as c 
FROM jobs j1, jobs j2
WHERE j1.code = j2.code AND j1.id <> j2.id
GROUP BY j1.code
;

ALTER TABLE mv_jobs ADD CONSTRAINT chk_mv_jobs CHECK( c = 1 ) ;

Note the value in the CHECK can be anything since the MV should always remain empty...