sql/create-sf1_blocks-table.sql

   1 /*
   2   We need to create a unique index on the referenced
   3   foreign key column before we can create the foreign key
   4   constraint.
   5 */
   6 CREATE UNIQUE INDEX idx_tiger_blkidfp00_unique
   7        ON tiger (blkidfp00);
   8
   9 CREATE TABLE sf1_blocks (
  10        id                 SERIAL           PRIMARY KEY,
  11        state              varchar(2)       NOT NULL,
  12        county             varchar(3)       NOT NULL,
  13        tract              varchar(6)       NOT NULL,
  14        block              varchar(4)       NOT NULL,
  15        arealand           double precision NOT NULL,
  16        areawatr           double precision NOT NULL,
  17        tiger_blkidfp00    varchar(15)      NOT NULL REFERENCES tiger (blkidfp00),
  18        pop100             integer          NOT NULL,
  19        total_area         double precision NOT NULL,
  20        population_density double precision NOT NULL
  21 );
  22
  23 CREATE UNIQUE INDEX idx_sf1_blocks_tiger_blkidfp00_unique
  24        ON sf1_blocks (tiger_blkidfp00);