Re: CONSTRAINT ... FOREIGN KEY

Thanks, it is neer..
What about FOREIGN KEY here is an example:

CREATE TABLE partner
(
    aid                serial CONSTRAINT partner_aid_key PRIMARY KEY NOT
NULL,
    ...
);

CREATE TABLE foo
(
    aid                serial CONSTRAINT goodies_aid_key PRIMARY KEY NOT
NULL,
    ...
    partner_ref    INTEGER NOT NULL,
    CONSTRAINT foo_partner_key FOREIGN KEY (partner_ref) REFERENCES partner
(aid)
);

For table "foo" would be created a key "foo_partner_key" implicitly ?

Best Regards
    tövis

----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "tövis" <tovises@freemail.hu>
Cc: "pgsql novice" <pgsql-novice@postgresql.org>;
<operationsengineer1@yahoo.com>
Sent: Tuesday, May 03, 2005 12:42 PM
Subject: Re: [NOVICE] CONSTRAINT ... FOREIGN KEY



On May 3, 2005, at 5:49 AM, tövis wrote:

> Not, yet;-(
> For me the connection between two tables - database relation - based on
> two KEYs one in the "FOREIGN" table one in the "NATIV" table, other way
> how to deside which rows are "linked" to foreign row (from this point of
> view "parent" or many-to-one relation). Other words - KEYs on both tables
> are must be!
> In SQL language it is not obviose, that I cretae a FOREIGN KEY- REFERENCES
> to a UNIQUE column in other table, which is not a PRIMERY KEY or UNIQUE
> INDEX in foreign table. Other way how server will find, as quickly as
> possible, the related row in the related table?
> Sincerelly
>   tovis
>

When you declare a column to be unique or to be the primary key, an
index IS created and is used to look up the value of the foreign key,
as it looks like you expected it to do.  I hope I am interpreting your
question correctly.

> PS: also if I create a column as UNIQUE how the server will deside that
> newly inputed value is unique to this column, without create a key/index -
> linear search on several million records?!
>

When you create a unique key, the index is created implicitly, as the
message usually says when the index is created.

> ----- Original Message ----- From: <operationsengineer1@yahoo.com>
> To: "tövis" <tovises@freemail.hu>; "pgsql novice"
> <pgsql-novice@postgresql.org>
> Sent: Tuesday, May 03, 2005 4:30 AM
> Subject: Re: [NOVICE] CONSTRAINT ... FOREIGN KEY
>
>
>> --- tövis <tovises@freemail.hu> wrote:
>>> Creating/adding a "FOREIGN KEY" creates any "real"
>>> key sequencies?
>>
>> tovis, i'm not sure of the technical details, but a
>> link is created between the foreign key and the column
>> it references.
>>
>>> Foreign key constraint became a key - seem to me,
>>> but what about the foreign table, there is also
>>> created a "seemless" key or I should create somthing
>>> (of course if it is not the primery key) - index?
>>> Sincerelly
>>>     tovis
>>
>> the foreign key should most definitely reference
>> another column in another table.
>>
>> your question is a little hard for me to understand -
>> i hope i answered it.
>>
>> __________________________________________________
>> Do You Yahoo!?
>> Tired of spam?  Yahoo! Mail has the best spam protection around
>> http://mail.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend