Re: CONSTRAINT ... FOREIGN KEY

On May 3, 2005, at 8:03 AM, tövis wrote:

> 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 ?\

No.  If you need an index on partner_ref, you will need to create it
explicitly.  In general, you don't need to do that unless you are
looking up by partner_ref.

>> 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.
>> 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
>