FK refrences PK , what that means?

[Tony]

Hello.
I was in need of a help on building a table that has a category with nested
sub category. I managed to get this help to build the table using SQL
statment.
table structure seems to be very easy. but to do the nested subcat.. it
seems FK refrences PK. what that dose and what exactly that means. ? as i
mentioned i have only single table in my database.
table has 3 field
ID = PK
Name = Text
Parent_id = FK
Statment is :
Create table categories
( id integer not null constraint cat_pk primary key
,name text(50) not null
,constraint parent_fk foreign key (parent_id) refrences categories (id)
)
thats all to it.
any info on refrencing PK from FK would greatly help me to undrestand this
process.
thanks
ps: table name is Categories. i know in order to have FK in my categories
table , i need to have a another table in database that i can use PK of that
table as FK to categories. but i have only one table .

 

[Steve Huff]

This question sounds like a homework problem because not many people want to
create FK's and PK's when they don't even know what they are. Typically you
will need more then one table. A Foreign Key is a key reference from
another table, usually a primary key.

--Steve Huff

 

[Dirk Goldgar]

Hello.
I was in need of a help on building a table that has a category with
nested sub category. I managed to get this help to build the table
using SQL statment.
table structure seems to be very easy. but to do the nested subcat..
it seems FK refrences PK. what that dose and what exactly that means.
? as i mentioned i have only single table in my database.
table has 3 field
ID = PK
Name = Text
Parent_id = FK
Statment is :
Create table categories
( id integer not null constraint cat_pk primary key
,name text(50) not null
,constraint parent_fk foreign key (parent_id) refrences categories
(id) )
thats all to it.
any info on refrencing PK from FK would greatly help me to undrestand
this process.
thanks
ps: table name is Categories. i know in order to have FK in my
categories table , i need to have a another table in database that i
can use PK of that table as FK to categories. but i have only one
table .

You are mistaken. It is possible to have a foreign key that references
the primary key of the same table, and this situation -- categories and
subcategories -- is a good example. Parents and children being stored
in a table of People is another example.

It's not clear what you're asking here. You have the SQL to create the
table. All the PK/FK relationship means in this table is that some
categories are subcategories of other categories. That doesn't seem
hard to understand.

 

[Tony]

i do undrestand the relation between FK and PK. but one thing is confusing
is that . there is a PK and FK in a single table and FK refrences the PK in
same table.
I am used to seeing this amoung two or more tables. but in this case both
key are in same table that refrence each other.
thats something i can not undrestand.
slowley but surely with catch on

 

[John Vinson]

i do undrestand the relation between FK and PK. but one thing is confusing
is that . there is a PK and FK in a single table and FK refrences the PK in
same table.
I am used to seeing this amoung two or more tables. but in this case both
key are in same table that refrence each other.
thats something i can not undrestand.
slowley but surely with catch on

That's why it's a *SELF* join. You're joining the table *to itself*.

A subcategory (of a sub-subcategory, or a sub-sub-sub-sub-subcategory)
*is a category*; it's just in a particular kind of relationship to
other categories. So there's only one table with all the categories
(of whatever level) in it.

The Foreign Key simply points to the category "the next level up", the
category of which *this* category is a sub.

Toplevel categories simply have a NULL in the Foreign Key field
(they're not subs of anything).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps