Thread: CONSTRAINT ... FOREIGN KEY

CONSTRAINT ... FOREIGN KEY

From
tövis
Date:
Creating/adding a "FOREIGN KEY" creates any "real" key sequencies?
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
 

Re: CONSTRAINT ... FOREIGN KEY

From
Date:
--- 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

Re: CONSTRAINT ... FOREIGN KEY

From
tövis
Date:
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

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

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


Re: CONSTRAINT ... FOREIGN KEY

From
Sean Davis
Date:
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
>


Re: CONSTRAINT ... FOREIGN KEY

From
tövis
Date:
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


Re: CONSTRAINT ... FOREIGN KEY

From
Sean Davis
Date:
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
>