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_blocks_blkidfp00_unique
   7        ON tiger_blocks (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        blkidfp00          varchar(15)      NOT NULL REFERENCES tiger_blocks (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_blkidfp00_unique
  24        ON sf1_blocks (blkidfp00);