CODEWITHSUNDEEP
databaseone-to-one
Roobah
Roobah

Reputation: 321

Table has one to one relationship with many tables

1) Can a table have a one-to-one relationship with a number of tables !?

To clarify more, if I want to do an insert the first table will be affected and only one of the other tables will be affected .

2) and if so, how the primary key will be ?

3) also, what will the query look like if I want to retrieve a number of records from these tables ?

Thank you .

Upvotes: 4

Views: 3912

Answers (2)

Thomas
Thomas

Reputation: 64655

Can a table have a one-to-one relationship with a number of tables !?

Yes if you really mean 1:0/1:

Create Table Parent
    (
    Id ... not null Primary Key
    , ...
    )

Create Table Child1
    (
    Id ... not null Primary Key
    , Foreign Key ( Id ) References Parent ( Id )
    ...
    )

Create Table Child2
    (
    Id ... not null Primary Key
    , Foreign Key ( Id ) References Parent ( Id )
    ...
    )

This setup would require entering a value into the Parent table first and then the child tables (in no particular order) afterwards. Further, you could add a value to one of the Child tables and not the other since they both only rely on the existence of a value in the Parent table and not each other.

Addition

To select from your child tables, it would involve the same process as any other parent-child relationship. For example:

Select P.Col1, P.Col2...
    , Child1.Col1, Child1.Col2...
From Parent
    Inner Join Child1
        On Child1.FKCol = Parent.PKCol

By using an Inner Join here, i'm only return Parent rows where there exists a Child row. If you wanted all Parent rows and only those Child rows where there is a match, you would use a Left Join instead of an Inner Join. If you wanted to select data from more than one child table at the same time, you can simply include those in the From clause:

Select P.Col1, P.Col2...
    , Child1.Col1, Child1.Col2...
    , Child2.Col1, Child2.Col2...
    , Child3.Col1, Child3.Col2...
From Parent
    Left Join Child1
        On Child1.FKCol = Parent.PKCol
    Left Join Child2
        On Child2.FKCol = Parent.PKCol
    Left Join Child3
        On Child2.FKCol = Parent.PKCol

Upvotes: 7

Abe Miessler
Abe Miessler

Reputation: 85086

In SQL Server you could certainly design a database that is capable of representing this relationship. You could enforce the one to one relationship by having the child tables use the ParentId as their primary key and force uniqueness.

If you wanted to query a parent table and it's three children that may or may not have existing records your query would look something like this:

SELECT * FROM ParentTable as pt
LEFT JOIN ChildTable1 as ct1
ON pt.id = ct1.ParentId
LEFT JOIN ChildTable2 as ct2
ON pt.id = ct2.ParentId
LEFT JOIN ChildTable3 as ct3
ON pt.ID = ct3.ParentId

My question would be why would you break a one to one relationship into multiple tables? You could also enforce a one to one relationship with the data if you kept it all in one table. This would make for cleaner queries (no joins) and better performance.

Upvotes: 1

Related Questions