title: Are these FKs necessary -- and are they stopping me?
body: <p>I'm having some difficulties with a database I'm creating for a summer camp, specifically with the PK and FK constraints. When I declare the FK constraint (e.g. FOREIGN KEY(PID) references Campers(CamperID)) I get an error running my code through pgAdmin (I'm using PostgreSQL). I understand that, for example, the Campers table is not yet created, and this is most likely part/all of the roadblock, however I feel like my FKs are still wrong somehow. To my understanding, a FK is a PK in another table -- but I feel like there is some redundancy or disconnect between my tables.</p>
  
  <p>I've put the syntax for some of my CREATE statements below. I'm not sure if I'll get reprimanded for the quality of my (somewhat vague) question, but I feel a bit lost and would appreciate any help or advice. Thank you in advance!</p>
  
  <pre><code>DROP TABLE IF EXISTS People;
  CREATE TABLE People (
      PID                 VARCHAR(10) NOT NULL            UNIQUE,
      FName               TEXT                NOT NULL,
      LName                   TEXT                NOT NULL,
      DOB                     DATE                NOT NULL,
      ArrivalDate     DATE                NOT NULL            DEFAULT CURRENT_DATE,
      DepartureDate       DATE,               
      US_PhoneNum         VARCHAR(11)     NOT NULL,
      StreetAddress       VARCHAR(200)    NOT NULL,
      Sex                 GENDER          NOT NULL,
      ZIP                 VARCHAR(10) NOT NULL,
  PRIMARY KEY(PID),
  FOREIGN KEY(PID) REFERENCES Campers(CamperID),
  FOREIGN KEY(PID) REFERENCES Counselors(CounselorID),
  FOREIGN KEY(ZIP) REFERENCES Zip(ZIP)
  );
  
  DROP TABLE IF EXISTS Zip;
  CREATE TABLE Zip (
      ZIP     VARCHAR(10) NOT NULL,
      City        TEXT                NOT NULL,
      State       VARCHAR(2)      NOT NULL,
      PRIMARY KEY(ZIP)
  );
  
  DROP TABLE IF EXISTS Campers;
  CREATE TABLE Campers (
      CamperID        VARCHAR(10) NOT NULL REFERENCES People(PID),
      AgeGroup        AGES                NOT NULL,
      CabinID     VARCHAR(2)      NOT NULL,
      Bed         BEDTYPES            NOT NULL,
      GroupID     VARCHAR(3)      NOT NULL,
      PRIMARY KEY(CamperID),
      FOREIGN KEY(CamperID) REFERENCES People(PID),
      FOREIGN KEY(CabinID)  REFERENCES Cabins(CabinID),
      FOREIGN KEY(Bed)      REFERENCES Beds(Bed),
      FOREIGN KEY(GroupID)  REFERENCES Groups(GroupID)
  );
  
  DROP TABLE IF EXISTS Counselors;
  CREATE TABLE Counselors (
      CounselorID VARCHAR(10) NOT NULL REFERENCES People(PID),
      GroupID         VARCHAR(3)      NOT NULL,
      CabinID         VARCHAR(2)      NOT NULL,
      PRIMARY KEY(CounselorID),
      FOREIGN KEY(GroupID) REFERENCES Groups(GroupID),
      FOREIGN KEY(CabinID) REFERENCES Cabins(CabinID)
  );
  </code></pre>
  
  <p>ERROR message for further clarification:</p>
  
  <pre><code>ERROR:  relation "campers" does not exist
  ********** Error **********
  
  ERROR: relation "campers" does not exist
  SQL state: 42P01
  </code></pre>
  
  <p>There are more tables (obviously) which I can provide the create statements for, if needed. </p>