Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I suspect you want a city_id as the PK to join the two, rather than a name. There are lots of cities around the world with the same name, so best not use the name as the PK... –  FreshPhilOfSO Jan 27 '13 at 23:45
    
@Phil no :-) I took the foreign key creation example from the postgresql documentation postgresql.org/docs/9.1/static/tutorial-fk.html , in my personnal case, the PK is an artificial integer id, a serial. –  Stephane Rolland Jan 27 '13 at 23:49
    
Good question, it sure would be useful. –  trygvis Jan 28 '13 at 9:16
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
add comment

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
);
share|improve this answer
    
I prefer create domain over m4. But I keep m4 in mind, I didn't know about it. It may be useful one day :-) –  Stephane Rolland Jan 29 '13 at 23:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.