Thread: CONSTRAINT ... FOREIGN KEY
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
--- 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
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 >
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 >
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
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 >