Search Postgresql Archives

Re: Change key primary for key foreign

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Guilherme Rodrigues wrote:
> I created one table so:
> 
> CREATE TABLE clima (
>     city     char(80),
>     cprc     int,
> );
> 
> And have other table so:
> 
> CREATE TABLE city (
>     name            char(80),
>     other_thing     int,
> );

These SQL statements have syntax errors (comma after the last column).

> But now I want the table clima receive name table city as foreign key.
understand?
> Sorry my bad english.

You will need a UNIQUE constraint on the referenced column.
Since there is no primary key yet, we'll define one:

ALTER TABLE city ADD CONSTRAINT city_pkey PRIMARY KEY (name);

Then you need a FOREIGN KEY constraint on table "clima":

ALTER TABLE clima ADD CONSTRAINT clima_city_fkey FOREIGN KEY (city)
REFERENCES city(name);

But really, you need much more.
First, for performance reasons it is highly advisable that
you define an index on clima(city).
Then you should habe a primary key on each table.
You can roll that into one:

ALTER TABLE clima ADD CONSTRAINT clima_pkey PRIMARY KEY (city);

But I think that using the city name as primary key is
not a good idea to begin with.  There could be different cities
with the same name, for one.  So in that case it would be best
to define an "artificial primary key column", some integer
like "city_id" and "clima_id".
Then introduce a column "clima.city_id" and define the foreign key
on that column.

You probably still have to learn a few things about
physical table design.  I suggest that you read what the PostgreSQL
manual has to say about data definition:
http://www.postgresql.org/docs/9.2/static/ddl.html

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux