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?

[Serial Types](http://www.postgresql.org/docs/9.3/static/datatype-numeric.html) section Table 8-2. Numeric Types and 8.1.4. Serial Types — Rhim, Jul 29 '14 at 16:29
Oh. Thanks for the link. So SERIALs are indeed positive only, just that they are '31 bit(effectively)'. Great. Thanks. — 0fnt, Jul 29 '14 at 16:35

Erwin Brandstetter · Accepted Answer · 2023-01-08T19:07:03.503

13

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?

edited Jan 08 '23 at 19:07

answered Jul 29 '14 at 16:58

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you for taking the time to educate me! Duly noted all the points. I read up the manual about identifiers, but while I can choose to not use camel case, I still want some sort of distinction in table name and column name which makes things somewhat easier to understand. I know I'm being unconventional, but I think in a structured way to an end. Are there any potential downsides? – 0fnt Jul 30 '14 at 03:45
@user247077: Downsides in what exactly? – Erwin Brandstetter Jul 30 '14 at 13:41
Not following the convention.. ? – 0fnt Jul 30 '14 at 16:14
@user247077: I can't quite follow. `some sort of distinction in table name and column name`? What are you talking about? CaMeL case identifiers? – Erwin Brandstetter Jul 30 '14 at 16:25
Yes, I would like table to be CamelCase and column to be camelCase, hence distinguishing between them on the basis of the case of first char. – 0fnt Jul 30 '14 at 16:27
1

@user247077: You *can* do that. Either just type it that way in your code, without double-quotes around the name. All identifiers are lower-cased internally, everything should work, but there are caveats, like with dynamic SQL. Or double-quote identifiers to force CaMeL-case names. I would advice to do neither - makes your life easier. But that's just my friendly advice. – Erwin Brandstetter Jul 30 '14 at 16:31
Thanks. At this point I would continue with Camel case, but my stuff is pretty configurable, a string configures everything... so I can change later if there arises any issue. – 0fnt Jul 30 '14 at 17:18

score -1 · Answer 2 · answered Jul 29 '14 at 16:31

The right aproach is to read the manual.

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename ( colname SERIAL ); is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') );

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

SERIAL is basically an autoincrementing integer. If your data is bigger than int you should use bigint and countries table should use BIGSERIAL.

Okay, I wasn't aware SERIAL isn't positive only. I could have sworn I read something to that effect. — 0fnt, Jul 29 '14 at 16:34
@user247077: A default `serial` column is positive only. You can adapt it to insert negative numbers, too. — Erwin Brandstetter, Jul 29 '14 at 16:45

Foreign key of serial type - ensure always populated manually

2 Answers2

Linked