Cascading primary keys to child tables.

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

Database Design and Application Architecture

Cascading primary keys to child tables.

Author

Topic

OCD Dan
Starting Member

5 Posts

Posted - 2011-07-31 : 15:15:04

using sql server 2005

I have three tables with foreign keys, The top level table (facility) is doing what i want, it sends it's primary key (Facid)to the referenced table below it (physicalplant) when i add a row(manually by opening the facility table and adding data), physicalplant however is not sending it's key (ppid)to the referenced table below it (floorplans). I scripted the tables and was wondering if someone could take a look, or maybe help me out with a solution to get this working? it would seem like a simple thing to do but I'm stuck. thanks a lot. Dan

GO
/****** Object: Table [dbo].[Facility] Script Date: 05/01/2011 13:50:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Facility](
[PFI] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FacId] [int] NOT NULL,
[Facility] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[aCurrent] [bit] NULL,
[WhyInactivated] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FacilityType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FAddress] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCity] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Facility_FCity] DEFAULT ('New York'),
[FState] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Facility_FState] DEFAULT ('New York'),
[FPostalCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[County] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FacPhone2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FEmergencyPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FFax] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Medicare] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Medicaid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Provider] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OC] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TypeOwnership] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[beds] [int] NULL,
[DidFACMove] [bit] NOT NULL,
[DidFACChangedNames] [bit] NOT NULL,
[DateOfChange] [datetime] NULL,
[WasFACSold] [bit] NOT NULL,
[Notes] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DidFACMergeWithOther] [bit] NOT NULL,
[DateOfMerge] [datetime] NULL,
[Recorded] [datetime] NULL,
[aSelect] [bit] NULL,
[PW] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Closed] [bit] NOT NULL,
[Closure_Date] [datetime] NULL,
[BirthDate] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TrueFalse] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Facility] PRIMARY KEY CLUSTERED
(
[FacId] 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
/****** Object: Table [dbo].[PhysicalPlant] Script Date: 05/01/2011 13:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PhysicalPlant](
[Ppid2] [int] NULL,
[FacId] [int] NULL,
[Ppid] [int] IDENTITY(1,1) NOT NULL,
[UnitName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ppbuildname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ppbuildype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ppbuildDate] [datetime] NULL,
[LSCEd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Filename] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImageData] [image] NULL,
[FileType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
CONSTRAINT [PK_PhysicalPlant] PRIMARY KEY CLUSTERED
(
[Ppid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PhysicalPlant] WITH CHECK ADD CONSTRAINT [FK_PhysicalPlant_Facility] FOREIGN KEY([FacId])
REFERENCES [dbo].[Facility] ([FacId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PhysicalPlant] CHECK CONSTRAINT [FK_PhysicalPlant_Facility]

USE [LTC11]
GO
/****** Object: Table [dbo].[FloorPlans] Script Date: 05/01/2011 13:51:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FloorPlans](
[Ppid2] [int] NULL,
[Ppid] [int] NOT NULL,
[FacId] [int] NULL,
[tblid] [int] IDENTITY(1,1) NOT NULL,
[Floor] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SortBy] [int] NULL,
CONSTRAINT [PK_FloorPlans] PRIMARY KEY CLUSTERED
(
[tblid] 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].[FloorPlans] WITH CHECK ADD CONSTRAINT [FK_FloorPlans_PhysicalPlant] FOREIGN KEY([Ppid])
REFERENCES [dbo].[PhysicalPlant] ([Ppid])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FloorPlans] CHECK CONSTRAINT [FK_FloorPlans_PhysicalPlant]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 04:30:33

why dont you implemet insertion using t-sql code. dont open table and enter values through UI.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OCD Dan
Starting Member

5 Posts

Posted - 2011-08-01 : 06:58:55

yes, I'm trying to figure out how to do that, so far, maybe the solution is to use the output clause as i found in this article, any suggestions on how to code the insertion would be great, thank you for responding. Dan http://www.databasejournal.com/features/mssql/article.php/3598636/OUTPUT-clause-in-SQL-Server-2005.htm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 09:48:08

yep. you can use OUTPUT. make a try and post in case you face any issue,

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-01 : 11:21:05

Should get rid of the cascading Foreign Keys.

I don't allow them under any circumstances on my systems.

They cause unexpected data loss and difficult to track down bugs.

OCD Dan
Starting Member

5 Posts

Posted - 2011-08-02 : 14:09:27

Thanks Russell, I was doing some reading on why to not use cascade, some of it was about data getting deleted that was still needed for other purposes. I might be way off on this, but my database only goes four tables deep, there's about forty tables and if I delete a row from the top level table, it wipes out everything related to it (what i want)it seems to be working good. also found out i don't need to insert keys into tables below, what actually happened was i restructured the relationships a little and the triggers stayed behind on the top level table causing me to think i needed to push keys down, works great now without the old triggers, i don't join any tables in queries, everything is databound controls in vb.net2008, the keys are populating on there own, although i did learn how to trigger insertion two different ways. Alter table test, add check (ppid>-99999999999999) will do it. there was a trigger that worked too, I'll let you know if it all melts down, thanks all for the help!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-02 : 15:00:02

Consider this small (and contrived) example:

Create Table PaymentType (
	PaymentTypeID int identity (1, 1) not null primary key,
	[Description] varchar(32) not null unique
);

Create Table SalesOrder (
	SalesOrderID int identity (1, 1) not null primary key,
	CustomerID int not null, -- FK to customer table (not shown in this example)
	PaymentID int not null -- FK to Payment.PaymentTypeID
);

Create Table Payment (
	PaymentID int identity(1, 1) not nul primary key,
	Amount money not null,
	PaymentTypeID int not null, -- FK to PaymentType.PaymentTypeID
	SalesOrderID int not null -- FK to SalesOrder.SalesOrderID
);

Now, if these FKs were allowed to cascade on delete, what happens if I decide to delete the PaymentType correspomding to, say, "Credit Card"? Every order and all payment info that was previously recorded as a credit card order is gone. Forever.

One of the two primary reasons we have the key for in the first place have just been violated as well. (1) to disallow invalid data (2)to prevent accidental deletes.

Now, suppose this happens and we don't notice. Then a few days later the customer calls because his order never shipped. We have NO RECORD of the order in the first place, even though his card has already been charged.

So, what is the right way? Depends on the application, but taking our contrived example a bit further, suppose we stop accepting credit cards. Perhaps our PaymentType table should look like this

Create Table PaymentType (
	PaymentTypeID int identity (1, 1) not null primary key,
	[Description] varchar(32) not null unique,
	isActive bit not null default (1)
);

now we can set the bit to 0 and make it "go away" without deleting any data at all.

Finally, suppose we really DO want to delete every order with credit cards. Rather than allowing cascades and hoping we know what happened, we can write a stored procedure that does exactly what we want, and only what we want.

Create Proc usp_DeleteAllCreditCardOrders
AS
SET NOCOUNT ON;

DELETE	s
FROM	SalesOrder s
JOIN	Payment p
On	p.PaymentID = s.PaymentID
WHERE	p.PaymentTypeID = 1; -- assuming 1 is credit card

DELETE	Payment
WHERE	PaymentTypeID = 1; -- assuming 1 is credit card
GO

sure, we've done a little more work, but we've enforced the integrity of our data, and prevented accidental deletes. And we, as programmers understand EXACTLY what we're doing when we decide to delete data.

Sorry for the long-winded post. Hope it makes sense.

OCD Dan
Starting Member

5 Posts

Posted - 2011-08-03 : 09:06:33

Russell,
Thanks for the enlightenment on cascading deletes and thank you for your time spent, that helps a lot. I just tried disabling the cascading delete on a table and my vb program still runs ok, it just leaves behind the row that would have been deleted in the database. I'm running replication out to laptops, so i think for now, i'll get rid of the cascading deletes and then replicate out the stored procedures later, the users don't delete to much stuff, so I'll get rid of the delete buttons on the upper level tables and figure out the sp's to do the deletes later, I've been working on this for a year and a half, the old access database is read only now (for 3 weeks) and they are chomping on the bit to get in and use the new database, thank you very much for the help, i guess there's probably a way to mark this thread resolved or otherwise so i'll look at that too. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources