/*
  We need to create a unique index on the referenced
  foreign key column before we can create the foreign key
  constraint.
*/
CREATE UNIQUE INDEX idx_tiger_blkidfp00_unique
       ON tiger (blkidfp00);

CREATE TABLE sf1_blocks (
       id                 SERIAL           PRIMARY KEY,
       state		  varchar(2)	   NOT NULL,
       county             varchar(3)       NOT NULL,
       tract		  varchar(6)       NOT NULL,
       block     	  varchar(4)       NOT NULL,
       arealand           double precision NOT NULL,
       areawatr           double precision NOT NULL,
       tiger_blkidfp00    varchar(15)      NOT NULL REFERENCES tiger (blkidfp00),
       pop100		  integer          NOT NULL,
       total_area 	  double precision NOT NULL,
       population_density double precision NOT NULL
);

CREATE UNIQUE INDEX idx_sf1_blocks_tiger_blkidfp00_unique
       ON sf1_blocks (tiger_blkidfp00);
