1 to * relationship on the same table
-
I was trying to set up a 1 to * on the same table like the northwind employees example. Where they have the employee is also a manager.
Mine is set up with a Borrower and a Co-Borrower for a Finance application.
I cannot do a insert because of the relationship constraint. Can someone look at this and tell me what I am doing wrong.
Erik
Dam again!
-
Well this would be easier if you would tell us what the error is. And also show us the full foreign key definition & details which this screenshot does not tell us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
My guess is he's tied the BorrowerParentID as a reference to the ParentID, and required that the ParentID field not be null, which effectively prevents you from entering any data because a Parent record would have to exist first, and my guess is there aren't any records in the table as yet. Unless there's ALWAYS a parent borrower, AND that parent borrower is ALWAYS ALREADY IN THE SYSTEM, that kind of setup is doomed. You can have a constraint that ties the two together only when the ParentID field is NOT NULL, but not as a foreign key relationship AND not allowing a null. I'm not sure exactly what the best way to set that kind of thing up is, but I'm pretty sure I know why it's preventing the insert.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Too many possibilities to come to any real conclusions. We need more information from the OP...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
I did not use this type of implementation; however, there had been a couple of times that i have thought back on the Northwind database where employees are also managers.
The problem is that I cannot insert a record because of the FK constraint. so if i insert the FIRST employee then there is no way to insert a FK record at the same time.
CREATE TABLE [dbo].[Borrower](
[BorrowerID] [int] IDENTITY(1,1) NOT NULL,
[BorrowerParentID] [int] NOT NULL CONSTRAINT [DF_Borrower_BorrowerParentID] DEFAULT ((0)),
[IsCoBorrower] [bit] NOT NULL,
[FirstName] [varchar](30) NOT NULL,
[LastName] [varchar](30) NOT NULL,
[HomePhone] [varchar](30) NOT NULL,
[Mobile] [nchar](10) NOT NULL,
CONSTRAINT [PK_Borrower_BorrowerID] PRIMARY KEY CLUSTERED
(
[BorrowerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Borrower] WITH CHECK ADD CONSTRAINT [FK_Borrower_Borrower_BorrowerParentID] FOREIGN KEY([BorrowerParentID])
REFERENCES [dbo].[Borrower] ([BorrowerID])
GO
ALTER TABLE [dbo].[Borrower] CHECK CONSTRAINT [FK_Borrower_Borrower_BorrowerParentID]
ALTER TABLE [dbo].[Borrower] ADD CONSTRAINT [DF_Borrower_BorrowerParentID] DEFAULT ((0)) FOR [BorrowerParentID]
Dam again!
-
First item: your code, as shown, fails to even run correctly, as you appear to be creating both constraints twice. Once I had that straightened out, I discovered through testing that as long as you have a NOT NULL constraint on the foreign key field, the only way to insert your 1st record is to have it be it's own parent. If you instead allow NULLs as the AdventureWorks table you referred to does, you can start out right away with inserting records that have NULL as the parent id. Also, why have a DEFAULT of 0 for the foreign key? You could never succeed on an insert using default values anyway, unless you reset your identity field to start at 0 instead of 1. I thus eliminated that default constraint altogether. You just don't need it.
Also, I'm just wondering what good your bit field "IsCoBorrower" is going to do you? How do you plan to determine that any two borrowers in the table are co-borrowers? Unless you don't have more than one person doing the data entry, you run the risk of having consecutive records with this field as 1, yet they aren't co-borrowers. You might then think that the names would then match, but even that's not a guarantee, as what happens when a married couple shows up where the woman maintains her maiden name and is a co-borrower with her husband? This would appear to need to be yet another foreign key reference INT field instead of just bit. After I wrote that, I realized that it's parent ID field you're using, which means that there's no reason to include a child record if they're not a co-borrower, so you don't need that field either way.
Here's the code I used to successfully insert a record with the existing constraints except for the default one:
DROP TABLE dbo.Borrower
CREATE TABLE [dbo].[Borrower](
[BorrowerID] [int] IDENTITY(1,1) NOT NULL,
[BorrowerParentID] [int] NULL,
[IsCoBorrower] [bit] NOT NULL,
[FirstName] [varchar](30) NOT NULL,
[LastName] [varchar](30) NOT NULL,
[HomePhone] [varchar](30) NOT NULL,
[Mobile] [nchar](10) NOT NULL,
CONSTRAINT [PK_Borrower_BorrowerID] PRIMARY KEY CLUSTERED
(
[BorrowerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Borrower] WITH CHECK ADD CONSTRAINT [FK_Borrower_Borrower_BorrowerParentID] FOREIGN KEY([BorrowerParentID])
REFERENCES [dbo].[Borrower] ([BorrowerID])
GO
INSERT INTO dbo.Borrower (BorrowerParentID, IsCoBorrower, FirstName, LastName, HomePhone, Mobile)
SELECT CAST(NULL AS int), CAST(0 AS bit), 'Kilroy', 'WasHere', '(876) 555-1212','1234560987'
INSERT INTO dbo.Borrower (BorrowerParentID, IsCoBorrower, FirstName, LastName, HomePhone, Mobile)
SELECT CAST(1 AS int), CAST(0 AS bit), 'Fred', 'Gwynn', '(123) 456-7890','9876543210'
SELECT *
FROM dbo.Borrower
Steve
(aka smunson)
:):):)
AFCC Inc. Com (1/28/2009)
I did not use this type of implementation; however, there had been a couple of times that i have thought back on the Northwind database where employees are also managers.The problem is that I cannot insert a record because of the FK constraint. so if i insert the FIRST employee then there is no way to insert a FK record at the same time.
CREATE TABLE [dbo].[Borrower](
[BorrowerID] [int] IDENTITY(1,1) NOT NULL,
[BorrowerParentID] [int] NOT NULL CONSTRAINT [DF_Borrower_BorrowerParentID] DEFAULT ((0)),
[IsCoBorrower] [bit] NOT NULL,
[FirstName] [varchar](30) NOT NULL,
[LastName] [varchar](30) NOT NULL,
[HomePhone] [varchar](30) NOT NULL,
[Mobile] [nchar](10) NOT NULL,
CONSTRAINT [PK_Borrower_BorrowerID] PRIMARY KEY CLUSTERED
(
[BorrowerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Borrower] WITH CHECK ADD CONSTRAINT [FK_Borrower_Borrower_BorrowerParentID] FOREIGN KEY([BorrowerParentID])
REFERENCES [dbo].[Borrower] ([BorrowerID])
GO
ALTER TABLE [dbo].[Borrower] CHECK CONSTRAINT [FK_Borrower_Borrower_BorrowerParentID]
ALTER TABLE [dbo].[Borrower] ADD CONSTRAINT [DF_Borrower_BorrowerParentID] DEFAULT ((0)) FOR [BorrowerParentID]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Best guess is that the IsCoBorrower is nothing more than a syndication indicator, credit facilitators...
Nuther perspective for the default parent borrower would be that there may be a default parent borrower, but perhaps that should be another boolean indicator and populated with a trigger (or insert statement) as BOL says: "A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition".
Max
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply