Thread: Change key primary for key foreign

Change key primary for key foreign

From
Guilherme Rodrigues
Date:
Hello. I want to change one table already created. Where the comand? thank you.


Re: Change key primary for key foreign

From
Raymond O'Donnell
Date:
On 18/09/2012 21:37, Guilherme Rodrigues wrote:
> Hello. I want to change one table already created. Where the comand? thank you.

ALTER TABLE....

http://www.postgresql.org/docs/9.2/static/sql-altertable.html

What exactly do you want to do?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie


Re: Change key primary for key foreign

From
Guilherme Rodrigues
Date:
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,
);

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


Em terça-feira, 18 de setembro de 2012 17h37min59s UTC-3, Guilherme Rodrigues  escreveu:
> Hello. I want to change one table already created. Where the comand? thank you.



Re: Change key primary for key foreign

From
Edson Richter
Date:
Em 18/09/2012 21:25, Guilherme Rodrigues escreveu:
> 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,
> );
>
> But now I want the table clima receive name table city as foreign key. understand?
> Sorry my bad english.
>
>
> Em terça-feira, 18 de setembro de 2012 17h37min59s UTC-3, Guilherme Rodrigues  escreveu:
>> Hello. I want to change one table already created. Where the comand? thank you.
>>
>>
Guilherme,

You should define the appropriate constraints. Example:

create table city (name char(80) not null constraint PK_CITY primary
key, other_thing int);
create table clima ( city char(80) not null references foreign key
(name), cprc int);

But be aware that you should not create tables without primary keys,
your application would quickly become a mess.

If you search a bit, you will find excellent references on how to deal
with Foreign Keys.

Regards,

Edson.

<portuguese>
Olá, Guilherme!

Você deverá definir as contraints apropriadas nas tabelas. Na tabela
cidade, você deverá utilizar uma Primary Key que será referenciada pela
outra tabela.
Uma Foreign Key só pode referenciar uma coluna da tabela City se houver
uma Primery Key ou uma Unique Key associada aquela coluna.
Você também deve tomar o cuidado de não criar tabelas sem definir uma
Primary Key, pois elas são a garantia que não existirão registros
duplicados em seu sistema mais tarde.

Infelizmente, não podemos trocar e-mail diretamente em português (regras
da comunidade), mas existem centenas de exemplos que você pode consultar
na web. Pesquise sobre Foreign Key e você vai achar milhares de exemplos
básicos e avançados.

Abraço,

Edson
</portuguese>





Re: Change key primary for key foreign

From
"Albe Laurenz"
Date:
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


Re: Change key primary for key foreign

From
Guilherme Rodrigues
Date:
Em terça-feira, 18 de setembro de 2012 17h37min59s UTC-3, Guilherme Rodrigues  escreveu:
> Hello. I want to change one table already created. Where the comand? thank you.

Ok. Thank All.


Re: Change key primary for key foreign

From
Gavin Flower
Date:
On 19/09/12 19:40, Albe Laurenz wrote:
> 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
>
For example: I know of 2 cities named London, one in England, the other
in Canada.

Generally avoid primary keys that have real world significance as the
outside world might change them, or the values may not be actually be
unique.


Cheers,
Gavin