Confused with primary keys and foreign keys

Hi, I am currently doing a database project for my company. The database contains 5 tables inside, let's say A,B,C,D,E. The primary key of table A is referenced by all other tables. If an instance is created in other tables except in table A, it means that the foreign keys are not null. However, if an instance is created in table A, it doesn't necessarily mean that the primary key of that particular instance must be referenced by another instance in other tables. In other words, the foreign keys in other tables have null value.

Now, the question is how can I display this primary key in table A together with primary keys of other tables since some of them may not even exist yet? By displaying all the primary keys, I can therefore check that the primary keys in other tables still don't exist and with this, I can input a value to it.

P.S. I am connecting the database with VB 2005 Express Edition and I always get blank value when I tried to retrieve the value.

Thank you very much for the help

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table levelconstraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

|||

The basic problem is that IF there is not a row in the Child table (table with FK), or there is a row, but its FK value is NULL, there is no way to 'connect' the row to the Parent table (table with PK).

It is possible to 'simulate' the missing rows by using a LEFT JOIN, and referring to the Parent table first.

Try out this sample code to see how to combine tables where both tables have matching values, and also where one table is missing a FK value from the other table.

Code Snippet

--***********************************
--Problem: Demonstrate PK-FK JOINS

CREATE TABLE Parent
( RowID int IDENTITY PRIMARY KEY,
MyValue varchar(20)
)
GO

CREATE TABLE Child
( RowID int IDENTITY PRIMARY KEY,
ParentID int REFERENCES Parent(Rowid),
MyValue varchar(20)
)
GO

SET NOCOUNT ON

INSERT INTO Parent VALUES ( 'Parent1' )
INSERT INTO Parent VALUES ( 'Parent2' )
INSERT INTO Parent VALUES ( 'Parent3' )
INSERT INTO CHILD VALUES ( 2, 'Child1' )
INSERT INTO CHILD VALUES ( 3, 'Child2' )
INSERT INTO CHILD VALUES ( 2, 'Child3' )
INSERT INTO CHILD VALUES ( NULL, 'Child4' )

-- To find ONLY Parents AND their Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
JOIN Child c
ON p.RowID = c.ParentID

-- To find All Parents and Children (if Any)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID

-- To find Parents without Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

-- To find Children without Parents (Orphans)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
RIGHT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

DROP TABLE Child
DROP TABLE Parent

|||Thanks for both your suggestions, but I don't have any idea how to perform LEFT JOIN on 5 tables. Can you gimme some idea on this? Thanks..|||

Manivannan.D.Sekaran wrote:

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table level constraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

The child values are actually not null, they are only not yet born. However, I need to display both the parent table which already has a child and which has no child at all. I will try to use OUTER JOIN in the code and see how the result will be. Thanks a lot.