Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Postgres, like all SQL DBMS's, is very broken, but here's the "perfect world" version (where Postgres is actually a real relational DB. haha). You can probably extrapolate to an approximate real world version from here ;)
    Customer
      -----------
      customer_id   integer,
      name          text,
      country_id    integer,
      state_id      integer,
    Actually, those integers should be separate data types, but Postgres doesn't really support that. Moving on:
    Country
      -----------
      country_id    integer,
      name          text
     
      State
      -----------
      state_id      integer,
      name          text
     
      ValidCountryState
      -----------
      country_id    integer,
      state_id      integer
    Now let's assume that you have a state entry named "" (0-length string). This will be the valid state for all countries without states.

    Then you need a constraint along the lines of
    EXISTS
    ( SELECT country_id
      FROM ValidCountryState AS VCS, Customer AS C
      WHERE VCS.country_id = C.country_id
        AND VCS.state_id = C.state_id )
    Of course, Postgres doesn't actually support database-wide constraints. I think you may be able to fake this with a trigger or something, or you could (ick) check it programatically before inserts and updates.

    This isn't too terrible a solution, and it doesn't even require NULLs (which are at best avoided, and at worst a huge big disaster).

    BTW, This doesn't have anything to do with normalization. "Normalization" is not the same thing as "integrity", though a normalized database is a good first step towards ensuring data integrity.