Create table hierarchy?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create table hierarchy??

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 15:22:07

Hi, with the three tables below, I want to add relationships so that each Grandparent can spawn many parents and each Parent can spawn many children.

I keep getting an error when I try to connect them. Could I get some help creating that hierarchy please?

Thanks in advance!

Here are the tables:

CREATE TABLE [dbo].[GrandParent] (
[GPID] [int] IDENTITY (1, 1) NOT NULL ,
[GPName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Parent] (
[PID] [int] NOT NULL ,
[GPID] [int] NOT NULL ,
[PName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child] (
[CID] [int] NOT NULL ,
[PID] [int] NOT NULL ,
[ChName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--PhB

richard75013
Starting Member

19 Posts

Posted - 2007-10-26 : 15:34:57

not enough info on the issue, the tables look fine, are you getting an error when creating FK's or quering them or?

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 16:00:03

Just looking on how to connect them in the manner I explained.

--PhB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-26 : 16:02:36

What do you mean by connect? Foreign key constraints?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

richard75013
Starting Member

19 Posts

Posted - 2007-10-26 : 16:03:24

when you say connect, do you mean a simple select query? Also, you really should make the Parent.PID an identity and that will be the value you pass to the child table.

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 16:16:28

I mean relationships, yes with Foreign Keys, I guess. I just want to be able to create GP record that has many P records and each of those P records has many C records.

--PhB

richard75013
Starting Member

19 Posts

Posted - 2007-10-26 : 16:23:17

As you can see you needed a PK on the 2 top level tables before you could add the FK and I changed the Parent table PID field to be an identity as you need it for RFI and for the PK.

CREATE TABLE [dbo].[GrandParent] (
[GPID] [int] IDENTITY (1, 1) NOT NULL ,
[GPName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, constraint PK_GrandParent_GPID primary key clustered (GPID)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Parent] (
[PID] INT IDENTITY (1, 1) NOT NULL ,
[GPID] [int] NOT NULL ,
[PName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, constraint PK_Parent_PID primary key clustered (PID)
, constraint FK_Parent_GrandParent_GPID foreign key (GPID) references GrandParent (GPID)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child] (
[CID] [int] NOT NULL ,
[PID] [int] NOT NULL ,
[ChName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, constraint FK_Child_Parent_GPID foreign key (PID) references Parent (PID)
) ON [PRIMARY]
GO

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-10-26 : 16:38:52

Thanks alot, much appreciated!

--PhB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources