No unique constraint matching given keys also ends up making relations non-existant

CREATE TABLE housing (
  name varchar(15),
  address varchar(15),
  city varchar(15),
  state varchar(15),
  zip integer,
  PRIMARY KEY(address, zipcode)
);

CREATE TABLE room (
  num integer PRIMARY KEY,
  capacity integer,
  name varchar(15) REFERENCES housing(name)
);

I have these two tables that are supposed to be created. The first one gets created just fine, but when the second one tires to get created, it tells me that there is no unique constraint matching given keys for table "building"

I'm pretty new to postgresql, so I honestly have no idea. I've searched around here and found some similar stuff, but those just said that the field specified wasn't unique enough, which I don't really understand yet (and is also exactly what the error says). Am I not referencing the name of the building specifically enough or what?

When creating tables further down the line that use the room table, it tells me that the room relation does not exist, but I assume it deals with this one error.

Any help would be appreciated.

Solution

The first one ("housing") can't possibly be created. Try it by itself.

To fix this, you need to change the structure of "housing", or change the structure of "room". Here's one way to fix the structure of "housing". (Don't do these fixes--there's really more to do than making a foreign key work.)

CREATE TABLE housing (
  name varchar(15) not null unique,
  address varchar(15),
  city varchar(15),
  state varchar(15),
  zipcode integer,
  PRIMARY KEY(address, zipcode)
);

The other columns should probably be declared not null, too.

Here's one way to fix the structure of "room".

CREATE TABLE room (
  num integer PRIMARY KEY,
  capacity integer,
  address varchar(15) not null,
  zipcode integer not null,
  foreign key (address, zipcode) references housing (address, zipcode)
);

Now, to some of the other problems. The original structure of the tables included this attempt at a foreign key reference.

name varchar(15) REFERENCES housing(name)

This implies you believe that "name" identifies a "housing". In the original table, though, "name" does not identify a "housing"; the pair of columns "address" and "zipcode" identifies a "housing". Now, I don't know what "name" means here, and I'm not inclined to guess. It might be the name of a housing (whatever that means), it might be the occupant's name, it might be some other kind of name. In any case, give "name" a better name.

In the US, addresses can be up to 64 characters long, according to the US Postal Service. Some city names are longer than 15 characters. ZIP codes are not integers. ZIP codes can have leading zeroes; integers cannot. Use char(5) for a five-digit ZIP code. Also consider using a regular expression inside a CHECK constraint to make sure that only the digits 0-9 can be used in the ZIP code.

Using num for the primary key of "room" is probably a bad idea. If it refers to an actual room number, then you can have only one room 100 in your database. If it doesn't refer to an actual room number, then you can't reasonably identify a room. This table needs more work. You need a CHECK constraint on capacity to guarantee that it's greater than zero.