Is there a way to avoid duplicating the definition of a foreign key?

Question

I was wondering about this example

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

The definition of city as varchar(80) is duplicated here. Is there a PostgreSQL syntax that allows not to duplicate varchar(80), only basing itself on references cities(city) so as to create the weather table?

Answer 1

There is no way to do that. And, frankly, I don't see the need.
Remember that referenced and referencing column don't have to share the same data type. They just have to have an = operator defined between them.

If your aim is to shorten the syntax, you could omit the column (or column list) of the referenced column(s) if it's the PK:

CREATE TABLE weather (
    city      text references cities,
    temp_lo   int,
    ...

Data type in the example is compatible but not the same (textvs. varchar(80)), referenced column is assumed to be the primary key of the table cities.

I quote the manual on CREATE TABLE:

If refcolumn is omitted, the primary key of the reftable is used

Answer 2

You can avoid duplicating the type definition, but SQL won't deduce the type to use by reading the type of a foreign key reference.

The SQL standard way

CREATE DOMAIN lets you create a domain (duh) which the docs describe as "essentially a data type with optional constraints".

So, in your case, you could do something like this.

create domain CITY_NAME as varchar(80) NOT NULL;

Then, in your table definition, you can use CITY_NAME instead of varchar(80).

CREATE TABLE cities (
        city     CITY_NAME primary key,
        location point
);

CREATE TABLE weather (
        city      CITY_NAME references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

PostgreSQL also supports CHECK() constraints as part of the CREATE DOMAIN statement.

If the definition has to change, you can dump the data, change the CREATE DOMAIN statement in one place, drop the database, rebuild the schema, and reload the data. I don't think you can change a domain that's in use, but I could be wrong. I've only had to do that three or four times in 30 years.

The non-standard, codegeek way

The C language has a preprocessor. Languages that lack a preprocessor can use m4.

m4 gives you more flexibility than SQL's CREATE DOMAIN, but it has a broader scope. Sometimes that's a good thing, and sometimes it's not. The most important difference is that m4 will look for a token like "CITY_NAME" everywhere, not just in the places where a data type declaration is expected. A carefully considered naming convention helps a lot.

Define a macro and its substitution. Then write your CREATE TABLE statements just like above.

define(`CITY_NAME', `varchar(80)')
CREATE TABLE cities (
        city     CITY_NAME primary key,
        location point
);

When you run that through m4, you should get a CREATE TABLE statement that has "varchar(80)" in place of "CITY_NAME", like this.

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);