Re: 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
>