Romber

Database tables

romber_location.locations

• Purpose
• Definition
• Constraints
• Load

Purpose

This table provides a list of valid locations. This information is static and doesn't need a data entry page.

Definition

Source romber_locations_tbl.sql

create table romber_location.locations (
  location_code         romber_base.generic_code                not null,
  location_id           romber_location.location_id             not null
                          default nextval ( 'romber_base.generic_id_seq' ),
  location_name         romber_base.generic_name                not null,
  location_status_id    romber_location.status_id               not null,
  location_subtype_id   romber_meta.object_id                   not null,
  location_type_id      romber_location.type_id                 not null,
  valid_begin_timestamp romber_base.generic_timestamp           not null
                          default ( current_timestamp at time zone 'UTC' ),
  valid_end_timestamp   romber_base.generic_timestamp           not null
                          default date '9999-12-31' );

Constraints

Source romber_locations_pk.sql

alter table romber_location.locations
  add constraint romber_locations_pk
    primary key ( location_id );

Source romber_locations_uk1.sql

alter table romber_location.locations
  add constraint romber_locations_uk1
    unique (
      location_type_id,
      location_subtype_id,
      location_code );

Source romber_locations_uk2.sql

alter table romber_location.locations
  add constraint romber_locations_uk2
    unique (
      location_type_id,
      location_subtype_id,
      location_name );

Source romber_locations_fk1.sql

alter table romber_location.locations
  add constraint romber_locations_fk1
    foreign key ( location_id )
    references romber_meta.objects ( meta_object_id );

Source romber_locations_fk2.sql

alter table romber_location.locations
  add constraint romber_locations_fk2
    foreign key ( location_type_id )
    references romber_location.types ( location_type_id );

Source romber_locations_fk3.sql

alter table romber_location.locations
  add constraint romber_locations_fk3
    foreign key ( location_status_id )
    references romber_location.statuses ( location_status_id );

Source romber_locations_fk4.sql

alter table romber_location.locations
  add constraint romber_locations_fk4
    foreign key ( location_subtype_id )
    references romber_location.locations ( location_id );

Source romber_locations_ck1.sql

alter table romber_location.locations
  add constraint romber_locations_ck1
    check (( location_id = 0 and location_type_id = 0 )
      or ( location_id > 0 and location_type_id > 0 ));

Source romber_locations_ck2.sql

alter table romber_location.locations
  add constraint romber_locations_ck2
    check (( location_id = 0 and location_status_id = 0 )
      or ( location_id > 0 and location_status_id > 0 ));

Load

Source romber_locations_load.sql

insert into romber_location.locations (
    location_code,
    location_id,
    location_name,
    location_status_id,
    location_subtype_id,
    location_type_id )
  values ( '', 0, '', 0, 0, 0 );