Database design for storing historical and future dated data?

I have a requirement to create a system for storing organization information. My client does re-organization maybe once a year and changes the names and codes of the organization. There are cases where a change is set on a future date and should take effect immediately when that date comes.

Note that it is important to retain the org_unit id since it is referenced by many other tables.

My current design is

create table org_units (
    id                number generated by default as identity
  , code              varchar2(0030) not null
  , name              varchar2(0100) not null
  , type              varchar2(0010) not null
  , location_id       number references locations (id)
  --
  , 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
  , last_session_id number not null
  , constraint org_units_pk primary key (id,effective_start_date)
);

My questions:

1. How can I enforce uniqueness of the code and name per org_unit (based in ID). Meaning no 2 active org_unit should have the same code or same name. For ex:

2. Also for the same sample above, as a business rule, a previous org code and name should never be re-used but it would also be nice to have this validation.

The trick is going to be satisfying the requirement "no 2 active org_unit should have the same code or same name", since "active" is defined as not having an effective_end_date in the past (assuming you have no future dated records).

To accomplish this, simply use NULL for effective_end_date to represent a current/active record. Then create unique constraints on (code,effective_end_date) and (name,effective_end_date). Unique constraints consider NULL as a distinct value and will enforce that any given code have only one record with a NULL end date - in other words, only one record for any given code or name that is currently active.

alter table org_units add constraint uq_org_units_code unique (code,effective_end_date);
alter table org_units add constraint uq_org_units_name unique (name,effective_end_date);

There is also an issue with your PK - if you are populating ID as an identity column, then you can't historically version ID. Each new historical row for a given code would get a new ID. If that's what you want, redefine the PK as ID only, without the start date in it.

Just a tip, though, it is highly unusual to have three candidate keys. Most likely (just judging from your column names) you should leave name as a normal non-key-candidate attribute and not attempt to put a constraint on it. code is enough of a key for programmatic use, and ID of course permits effective relational integrity (FKs) and joins. For me, name is merely an attribute that would be for presentation purposes only, so there would be no pressing reason to enforce uniqueness on it. Plus, if you ever needed to swap the names for two different codes, you wouldn't be able to do that without disabling the constraint first or adding a third step to change one to a bogus value first before later setting it to its final value. Nasty stuff, so best not have such a constraint at all. You're far less likely to ever do that to the code col, and your code probably requires uniqueness on it to not break, so that one makes good sense. Only enforce what you have to to ensure that things work. Don't try to over-constrain with unnecessary rules.