How to setup Parent>>Child>>Child relationship...?

Hello,

SQL newby looking for some advice. I have created the three tables below. XXParent is the master table, XXParentChild is the child table to XXParent and it should have a one-to-many relation to its parent. XXParentChildChild is the child table to XXParentChild, and it will likewise have a one to many relation to XXParentChild. In effect one XXParent row can have many XXParentChild rows assigned to it and one XXParentChild row can have many XXParentChildChild rows assigned to it.

What I'm missing is how to create the table so that once I've entered a row in XXParent, I can insert multiple rows in XXParentChild and subsequently insert multiple rows in XXParentChildChild for each of its parent rows, while maintaining referential integrity.

First, not sure what record id style to use, whether IDENTITY, or UNIQUEID, etc..
Second, not sure how to set up the FK's and Relationships between the tables.

Any advice appreciated greatly!!

Thanks in advance!

CREATE TABLE [XXParent] (

[XXSuiteID] [int] IDENTITY (1, 1) NOT NULL ,

[XXDateRun] [datetime] NULL ,

[XXStartTime] [datetime] NULL ,

[XXEndTime] [datetime] NULL ,

[XXsSucceeded] [int] NULL ,

[XXsWarned] [int] NULL ,

[XXsFailed] [int] NULL ,

[XXMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXClientMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXLogin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXLabel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

CONSTRAINT [PK_XXSuite] PRIMARY KEY CLUSTERED

(

[XXSuiteID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [XXParentChild] (
[XXSuiteID] [int] NOT NULL ,
[XXID] [int] IDENTITY (1, 1) NOT NULL ,
[XXIDInternal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXTier] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXStart] [datetime] NULL ,
[XXEnd] [datetime] NULL ,
[XXWFBTime] [datetime] NULL ,
[XXWFBCalled] [int] NULL ,
[XXSearches] [int] NULL ,
[XXSearchesTime] [datetime] NULL ,
[XXResult] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [XXParentChildChild] (

[XXID] [int] NOT NULL ,

[XXMssgType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXMessage] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GOAnswered my own question:

CREATE TABLE XXParent (
XXSuiteID int IDENTITY (1, 1) NOT NULL,
XXDateRun datetime NULL ,
XXStartTime datetime NULL ,
XXEndTime datetime NULL ,
XXsSucceeded int NULL ,
XXsWarned int NULL ,
XXsFailed int NULL ,
XXMachine varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXClientMachine varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXLogin varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXLabel varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT PK_XXSuite PRIMARY KEY CLUSTERED
(
XXSuiteID
) ON PRIMARY
) ON PRIMARY
GO

CREATE TABLE XXParentChild (
XXID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
XXSuiteID int NOT NULL ,
XXIDInternal varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXDescription varchar (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXTier text COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXNo varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXStart datetime NULL ,
XXEnd datetime NULL ,
XXWFBTime datetime NULL ,
XXWFBCalled int NULL ,
XXSearches int NULL ,
XXSearchesTime datetime NULL ,
XXResult varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
) ON PRIMARY TEXTIMAGE_ON PRIMARY
GO

CREATE TABLE XXParentChildChild (
XXCHILDID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
XXID int NOT NULL ,
XXMssgType varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXMessage varchar (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FOREIGN KEY (XXID) REFERENCES XXParentChild(XXID)
) ON PRIMARY
GOsql

How to set relationship level when creating diagram?

lets say I have 3 levels of relational table

Level 1 is the master table

Level 2 has 10 tables and all are children table of level 1

level 3 has 100 tables and all are children of tables in level 2

And I am trying to use MS SQL 2005 to create a diagram.

In SQL2000 I used to able to set the relationship level. For example, if I set the level = 0 it will only add the master table. If I set the level = 1 it will add the master table and those 10 tables links to the master table.... so on so forth.

But now in SQL2005 when I choose "Add related tables" I will have to let those 112 tables all squashed in a tiny 17" screen and it become very unmanagable.

Is there any way I can set the relationship level again in SQL2005?

It appears that we did not implement this behavior. Could you file this as an issue on Microsoft Connect? http://connect.microsoft.com/SQLServer/. We will triage it and let you know if/when we can put it into a future release of the product.

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

How to set a relationship here?

How to create a relation between gf_game and gf_gamegenre here? gf_gamegenre is responsible for the relation between a game and it's genre(s). The relationship between gf_genre and gf_gamegenre worked. (http://img361.imageshack.us/my.php?image=relationzl9.jpg)

When I try to set a relationshop between gamegenre and game I'm getting this error:

'gf_game' table saved successfully
'gf_gamegenre' table
- Unable to create relationship 'FK_gf_gamegenre_gf_game'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_gf_gamegenre_gf_game". The conflict occurred in database "gamefactor", table "dbo.gf_game", column 'gameID'.

Thanks for any help!

Shouldn't gf_gamegenre have a composite primary key of genreID and gameID? Then you would link gf.genreID with gf_gamegenre.genreID and gf_game.gameID with gf_gamegenre.genreID