What is a self referencing table

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

hi friends

i have a question about self referencing tables. first what is a self referencing tables

(Could you please give an example ) and second should i have two primary keys in a self referencing table? third how could i bind drop down list into a self referencing table?
for example a drop down for city and a drop down for province ? pleas help me .

Posted 15-Oct-14 6:09am

Member 11155615

Add a Solution

Comments

Sergey Alexandrovich Kryukov 15-Oct-14 12:24pm

The meaning should be obvious from the name. :-)
—SA

vbmike 15-Oct-14 12:35pm

This link gives an example (albeit somewhat confusing) See if it makes sense to you then pursue more in google.....
http://www.w3resource.com/sql/joins/perform-a-self-join.php

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2014-10-15T06:30:00

First of all, you can never have two primary keys: http://en.wikipedia.org/wiki/Unique_key[^].

Don't confuse this statement with the situation when you can have a compound key, which can be uses as a primary key, but it still has to be unique: http://en.wikipedia.org/wiki/Compound_key[^].

In you use a unique compound key as a primary key, you still have only one primary key per table, even though such key uses more than one table columns.

As to "self-referencing tables", I don't think this is any special term or a notion. The existence of self-referencing tables is a trivial consequence of relational model. I have no idea what do you want to know about them and why. There is nothing wrong with self-referencing. I guess, it would be better to use your brain to analyze self-referencing situations. You can look here: http://bit.ly/1u5magb[^].

—SA

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2014-10-15T07:45:00

Solution 2

Quote:
for example a drop down for city and a drop down for province ?

This is very simple. Two tables that's it.

1. City - CityId, CityName
2. Province - ProvinceId, FK_CityId, ProvinceName

While binding DropDownList for City, directly bind from City Table. The DropDownList for Province should be bound when some City is selected. So, you need to bind inside the SelectedIndexChanged Event of City DropDownList. You can also go for Ajax CascadingDropDowList. Refer - Ajax Cascading Dropdownlist Sample with database using asp.net[^]

Posted 15-Oct-14 7:45am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

Sergey Alexandrovich Kryukov 15-Oct-14 14:11pm

5ed.
—SA

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 15-Oct-14 14:13pm

Thanks Sergey. :)

[no name] 15-Oct-14 16:33pm

Member 11155615 at 28 mins ago

one question remains . when i wanna bind drop down list
should i youd selected value or selected index ? and how could i insert these data in two related tables

Tadit can you please help OP. I only know Little bit SQL ;)

Thanks, regards, Bruno

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 15-Oct-14 23:18pm

Sure Bruno. Thanks. :)

Maciej Los 15-Oct-14 16:53pm

+5!

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 15-Oct-14 23:17pm

Thanks Maciej. :)

User 11060979 · Accepted Answer · 2014-10-15T08:01:00

Solution 3

A self referencing table is a table which has a field which is defined as foreign key to the same table's primary key.

SQL

CREATE TABLE DBREGISTRY
(
  ID		INTEGER,
  PARENT        INTEGER,    // PARENT references ID of table DBREGISTRY 
  DESCRIPTION   CHAR(30),
  INTVALUE      INTEGER,
  CONSTRAINT    PK_DBREGISTRY PRIMARY KEY (ID),
  CONSTRAINT	FK_DBREGISTRY_PARENT FOREIGN KEY (PARENT) REFERENCES DBREGISTRY (ID)
)

This kind of table does allow to save data rows in a hierarchical way.

ID    PARENET     DESCRIPTION
1     1           "ROOT"
2     1           "FIRST LEVEL ITEM A"
3     2           "ITEM 1 OF FIRST LEVEL ITEM A"
4     1           "FIRST LEVEL ITEM B"
5     4           "ITEM 1 OF FIRST LEVEL ITEM B"

For imaging the hirarchy:

"ROOT"
     "FIRST LEVEL ITEM A"
          "ITEM 1 OF FIRST LEVEL ITEM A"
     "FIRST LEVEL ITEM B"
          "ITEM 1 OF FIRST LEVEL ITEM B"

There are several things (some of them are pitfalls) to pay attention in praxis:
a.) For foreign key parent it makes sense to define also "ON DELETE CASCADE"
b.) Importing such a table is usually not an easy thing, this because items and there ID can be arise completely random.

E.g. : Windows registry seems to be made with a similar structure.

Posted 15-Oct-14 8:01am

User 11060979

Updated 15-Oct-14 8:48am

v5

Comments

Sergey Alexandrovich Kryukov 15-Oct-14 14:11pm

5ed for the detailed example.
—SA

[no name] 15-Oct-14 14:11pm

Thank you very much, regards Bruno

[no name] 15-Oct-14 14:57pm

And sorry to all who recognized every minute an update of this answer. I was fighting with the layout of this answer :-) Hope I know for next time how to write it in one piece.

Member 11155615 15-Oct-14 16:03pm

thank you so much . but one question remains . when i wanna bind drop down list
should i youd selected value or selected index ? and how could i insert these data in two related tables

[no name] 15-Oct-14 16:09pm

Thank you for accepting. For binding a drop down list please wait for the answer of Tadit Dash.

I only know the SQL part and have no big knowledge of the GUI part....sorry.
Bruno

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 15-Oct-14 23:23pm

To insert data to these tables, you have to make some other page or interface. Because these are master tables. So, you can make one page where you will have TextBoxes for a City Name and other additional TextBoxes for the provinces. When user enters all, then you execute query to database and first store the City in City Table. Get the Id of this inserted City. Now insert to the Province Table with the inserted City Id as a Foreign Key.

While binding the DropDowns, the Name should bind to as DropDown text and ID should bind as DropDown value. So, when some option is selected on DropDown, then inside the SelectedIndexChanged Event, just get the DropDownList SelectedValue, which is the Id. Then make a query with this Id and get the related table data and bind to the other DropDownList.

Member 11155615 15-Oct-14 16:18pm

tnx :)

Maciej Los 15-Oct-14 16:52pm

+5!

[no name] 15-Oct-14 16:55pm

Thank you Maciej. Regards, Bruno

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 15-Oct-14 23:34pm

My 5 as well. :)

[no name] 16-Oct-14 2:26am

Thank you. Regards, Bruno

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 16-Oct-14 3:45am

:)

What is a self referencing table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

What is a self referencing table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us