CHECK Constraint not able to support Subquery?

See more:

SQL

database

PostgreSQL

Hey guys,

I am running into an issue when I try to set up a domain with a CHECK constraint that has a subquery in it. I am aware that apparently PostgreSQL does NOT support subqueries in CHECK clauses, as I got the error when trying to run the query. But I am trying to figure out if there is an alternative method to accomplishing this with PostgreSQL.
This is what I believe is the issue:

"CREATE DOMAIN hotelNoDD as hotelNoD
CHECK(VALUE IN(SELECT hotelNo FROM Hotel));"

Any ideas?

Here is my work so far:

Created Hotel Table:

CREATE DOMAIN hotelNoD AS CHAR(8);
CREATE TABLE Hotel(
hotelNo hotelNoD NOT NULL,
hotelName VARCHAR(30) NOT NULL,
city VARCHAR(40) NOT NULL,
PRIMARY KEY (hotelNo)
);

Attempted to Create Room Table:

CREATE DOMAIN roomNoD AS SMALLINT
    CHECK(VALUE BETWEEN 1 AND 100);
CREATE DOMAIN typeD AS CHAR(1)
    CHECK(VALUE IN('S', 'D', 'F'));
CREATE DOMAIN priceD AS DECIMAL(5,2)
    CHECK(VALUE BETWEEN 10 AND 100);
CREATE DOMAIN hotelNoDD as hotelNoD
    CHECK(VALUE IN(SELECT hotelNo FROM Hotel));

CREATE TABLE Room(
roomNo roomNoD NOT NULL,
hotelNo hotelNoDD NOT NULL,
type typeD NOT NULL,
price priceD NOT NULL,
PRIMARY KEY(roomNo, hotelNo),
FOREIGN KEY(hotelNo) REFERENCES Hotel
);