January 23, 2007 at 1:41 pm
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]
GO
January 23, 2007 at 2:51 pm
here's my suggestions:
I've added foreign keys to your script im bold below. I've always been taught it's good practice to have the Identity to be the first column of the table.
the foreign keys establish the parent-child-grandchild relationship.
you can explcily name the foreign keys, and the format would be sonething like this:
CONSTRAINT FK_XXPARENT_CHILD FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
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]
GO
Lowell
January 23, 2007 at 4:19 pm
Thanks so much, very kind of you.
One thing though, now I get a syntax error here:
CONSTRAINT FK_XXPARENT_CHILD FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
Should this be inside one of the table scripts? Or was that just an example that I can ignore?
Thanks again!
January 23, 2007 at 4:38 pm
yes i meant that you had a choice on the naming of a foreign key;
you can leave the CONSTRAINT CONSTRAINTNAME portion off, and SQL will automatically give it a unique name, or you can explicitly name it, which is a better practice, but obviously not manditory:
these are equivilent:
CREATE TABLE [XXParentChild] (
[XXID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
[XXSuiteID] [int] NOT NULL ,
...
FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [XXParentChild] (
[XXID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
[XXSuiteID] [int] NOT NULL ,
...
CONSTRAINT FK_XXPARENT_CHILD FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Lowell
January 23, 2007 at 5:16 pm
Yes, my bad, I misunderstood, sorry. What you gave me originally is working very good. Thank you!
One other question, please, if you don't mind:
I noted that the SQL Identity functions only return the top level Identity, not the ones generated on the child tables.
Is it better for me to generate my own sequential identities and force them on the respective columns using SET IDENTITY_INSERT DBO.XX... ON/OFF, thus giving me more control, or is there some trick to letting SQL do this?
Thanks again!
January 25, 2007 at 8:34 am
@@Identity basically returns the identity value for the last record your spid inserted. So, if you were inserting records you need to deal with them sequentially:
DECLARE @ParentID INT, @ChildID INT, @GrandchildID INT
Insert Parent (Name) VALUES ('MyName')
SET @ParentID = @@identity
INSERT Child (ParentID, Name) VALUES (@ParentID, 'MyChildName')
SET @ChildID = @@identity
INSERT GrandChild (ChildID, Name) VALUES (@ChildID, 'MyGrandChildName')
SET @GrandchildID = @@identity
January 25, 2007 at 9:00 am
Thank you, however I think I will go the route or tracking my own ID's through this stored proc I wrote. It gives me the next ID for the requested table. Then I will insert the ID using SET IDENTITY_INSERT ON/OFF on each insert command. I appreciate all the help, thank you.
CREATE PROC GetNewId(@TableName varchar(32)) AS
Declare @NextKey bigint
BEGIN TRAN
SELECT @NextKey = NewID from ID where TableName = @TableName
IF (SELECT @NextKey)IS NULL
BEGIN
INSERT INTO ID(NewID, TableName) VALUES(0, @TableName)
END
UPDATE ID
SET NewID = NewID + 1 Where TableName = @TableName
SELECT @NextKey = NewID from ID where TableName = @TableName
SELECT @NextKey as NextKey
COMMIT TRAN
GO
January 26, 2007 at 2:42 am
I'd suggest using Michael's method. Managing unique IDs yourself when sql is perfectly capable seems unnecessary to me...
Chris
January 27, 2007 at 9:36 am
Yes, you're right. But I'm concerned about concurrency issues and the possibility of identical ID's being generated by multiple instances.
MS has an article: http://support.microsoft.com/kb/163446/ even on Guaranteeing unique ID's and they suggest my method above. If MS puts out an article "guaranteeing" unique ID's, the insinuation is that the @@identity method does not always work.
But with my lack of in-depth SQL knowledge, I may be misunderstanding something.
In any event, thanks to all for your advice.
January 28, 2007 at 10:15 pm
DO NOT USE @@IDENTITY because it is not scope sensitive... use SCOPE_IDENTITY() instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply