Foreign key of serial type - ensure always populated manually

Question

I have two tables: countries and regions.

CREATE TABLE Countries(
    id     SERIAL,
    name   VARCHAR(40) NOT NULL,
    PRIMARY KEY(id)
)

CREATE TABLE Regions(
    id           SERIAL,
    countryId    SERIAL,
    name         VARCHAR(40) NOT NULL,
    PRIMARY KEY(id       ),
    FOREIGN KEY(countryId) REFERENCES Countries(id)
)

When I insert into regions, I would hope that if I fail to mention countryId, I would be stopped, however, countryId is automatically incremented. Is there any way I can be stopped automatically to make this insertion?

Following table where I set countryID as SERIAL NOT NULL doesn't solve the issue.

CREATE TABLE Pigeons(
    id           SERIAL,
    countryId    SERIAL NOT NULL,
    name         VARCHAR(40) NOT NULL,
    PRIMARY KEY(id       ),
    FOREIGN KEY(countryId) REFERENCES Countries(id)
)

The following solves the problem but I would think that it's technically incorrect because my serial could be bigger than 2^31, but int will never be >= 2^31.

CREATE TABLE Legions(
    id           SERIAL,
    countryId    INT NOT NULL,
    name         VARCHAR(40) NOT NULL
    PRIMARY KEY(id       ),
    FOREIGN KEY(countryId) REFERENCES Countries(id)
)

What's the right approach here?

Answer 1

I suggest:

CREATE TABLE country(
    country_id serial PRIMARY KEY
  , country    text NOT NULL
);

CREATE TABLE region(
    region_id  serial PRIMARY KEY
  , country_id int NOT NULL REFERENCES country
  , region     text NOT NULL
);

Don't use CaMeL-case identifiers if you can avoid it. See:

• Are PostgreSQL column names case-sensitive?

Use proper names. "id" or "name" are typically not descriptive enough (though "id" is a wide-spread anti-pattern used by various ORMs).

The underlying data type of a serial is integer. Make the referencing column integer.
Due to the foreign key reference region.country_id can only hold values that are present in country.country_id (or NULL, unless defined NOT NULL). Your considerations about values > 2^31 are uncalled for.
In Postgres 10 or later consider IDENTITY columns instead. See:

• Auto increment table column

Demonstrating short syntax for PK and FK definition (optional). Read the manual on CREATE TABLE.

Code example with more advice:

• How to implement a many-to-many relationship in PostgreSQL?