Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help With Foreign Keys Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 9:50 AM
Points: 9, Visits: 13
Hello,

I wondered if someone could explain to me how to setup foreign keys that cascade on update for the following tables -

tblLocations
Location varchar(255) PK

tblContainerMovements
MovementID int PK
MovementDate datetime
MoveFrom varchar(255) FK
MoveTo varchar(255) FK


Both a MoveFrom and a MoveTo is required by the database, if the location is changed in the master data table tblLocations it should cascade though the movement records in tblContainerMovements. But since the number of fields is different I can't set this up.


Would anyone like to help?

Thanks :)
Post #1350454
Posted Monday, August 27, 2012 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 9:50 AM
Points: 9, Visits: 13
PS. If it helps, this is what the tables currently look like -



USE [Tracker]
GO
/****** Object: Table [dbo].[tblLocations] Script Date: 08/27/2012 16:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblLocations](
[Location] [varchar](255) NOT NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_tblLocations_Active] DEFAULT ((1)),
CONSTRAINT [PK_tblLocations] PRIMARY KEY CLUSTERED
(
[Location] 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


AND...

USE [Tracker]
GO
/****** Object: Table [dbo].[tblContainerMovements] Script Date: 08/27/2012 16:23:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblContainerMovements](
[MovementID] [int] NOT NULL,
[MovementDate] [datetime] NOT NULL,
[MoveFrom] [varchar](255) NOT NULL,
[MoveTo] [varchar](255) NOT NULL,
[CustomerName] [varchar](64) NOT NULL,
[ContractReference] [varchar](64) NOT NULL,
[TNumber] [varchar](32) NOT NULL,
[NotesAndComments] [text] NULL,
CONSTRAINT [PK_tblContainerMovements] PRIMARY KEY CLUSTERED
(
[MovementID] 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].[tblContainerMovements] WITH CHECK ADD CONSTRAINT [FK_tblContainerMovements_tblTNumbers] FOREIGN KEY([TNumber])
REFERENCES [dbo].[tblTNumbers] ([TNumber])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblContainerMovements] CHECK CONSTRAINT [FK_tblContainerMovements_tblTNumbers]
GO
ALTER TABLE [dbo].[tblContainerMovements] WITH CHECK ADD CONSTRAINT [FK_tblContainerMovements_tblContractReferences] FOREIGN KEY([CustomerName], [ContractReference])
REFERENCES [dbo].[tblContractReferences] ([CustomerName], [ContractReference])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblContainerMovements] CHECK CONSTRAINT [FK_tblContainerMovements_tblContractReferences]
Post #1350457
Posted Monday, August 27, 2012 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
A simple search from google of BOL should provide you this type of stuff. A simple google search for "sql server foreign key cascade update" returned the following as the second or third listing.


http://msdn.microsoft.com/en-us/library/ms186973%28v=sql.105%29.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350464
Posted Monday, August 27, 2012 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 9:50 AM
Points: 9, Visits: 13
It doesn't seem to have the answer though that link.

It says "The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default."

Setting the same primary key to cascade to two columns seems to make SQL 2005 think there is cicular reference but their is not?
Post #1350467
Posted Monday, August 27, 2012 10:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
david_a_wallace (8/27/2012)
It doesn't seem to have the answer though that link.

It says "The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default."

Setting the same primary key to cascade to two columns seems to make SQL 2005 think there is cicular reference but their is not?


I missed that you were trying to use 2 FKs on the same table. It seems that your design is flawed. Your data is denormalized in what is generally considered bad practice. You have setup a primary key that can change. This is very challenging to say the least. Do you have permission to change the tables? If so, this seems like a reasonable place to use an identity field as your primary key. Then your foreign keys reference the row and you can change the text of it and it will always be current.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350483
Posted Monday, August 27, 2012 10:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
Looking at your problem a little bit you seem to not know how to properly reference other tables. You seem to routinely reference data so that is ends up being repeated. Also you should not use the text datatype, it is deprecated. http://msdn.microsoft.com/en-us/library/ms187993%28v=sql.90%29.aspx

Here is a shot in the dark about a more normalized structure given what you posted.

CREATE TABLE tblLocations
(
LocationID int identity not null,
Location varchar(255) NOT NULL,
Active bit NOT NULL CONSTRAINT DF_tblLocations_Active DEFAULT ((1)),
CONSTRAINT PK_tblLocations PRIMARY KEY CLUSTERED
(
LocationID 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

CREATE TABLE tblContainerMovements(
MovementID int NOT NULL,
MovementDate datetime NOT NULL,
MoveFromLocationID int NOT NULL,
MoveToLocationID int NOT NULL,
CustomerName varchar(64) NOT NULL, --This should be a FK to the CustomerID not their name
ContractReference varchar(64) NOT NULL, --This should reference the Contract by the PK not a text description
TNumber varchar(32) NOT NULL, --this appears to be poor RI again but not sure what it is.
NotesAndComments varchar(max) NULL, --don't use text datatype, it is deprecated. instead use varchar(max)
CONSTRAINT PK_tblContainerMovements PRIMARY KEY CLUSTERED
(
MovementID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350485
Posted Monday, August 27, 2012 3:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 9:50 AM
Points: 9, Visits: 13
Thanks for your help.

The problem I have with using IDs as keys is the user will see the IDs instead of the text on the main forums and would not be able to see where it was being moved from or to.

Would the design be OK if the user could not change the primary keys?
Post #1350606
Posted Monday, August 27, 2012 3:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
david_a_wallace (8/27/2012)
Thanks for your help.

The problem I have with using IDs as keys is the user will see the IDs instead of the text on the main forums and would not be able to see where it was being moved from or to.

Would the design be OK if the user could not change the primary keys?


That seems a bit odd but I don't know how you are using it. The point of a primary key is to uniquely identify a row. If a user is allowed to change the value of the primary key then it is probably not a good choice as the primary key. In general, you should not design your database around how users will view it. Seems to me the code that displays your data should be changed.

I can't completely tell because the code you posted originally did not contain all the tables but you have a foreign key to CustomerName. This is not a good idea at all. That type of schema can't handle two Customers with the same name. John Smith. There appears to be a lot of normalization issues in the tables you posted. Once you start coding lots of foreign key constraints with cascading updates you need to step back and look at your normalization. Why are you keys changing so much? The most logical answer there is that the columns you have chosen as primary keys are not a good choice.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350615
Posted Tuesday, August 28, 2012 5:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 9:50 AM
Points: 9, Visits: 13
OK I have tried to take the advice given (thanks for giving it) and change the database table structure, I have linked the tables into MS Access and drawn the links in the MS Access diagram (it is not showing the 1 to many connections etc but I think they are obvious enough). Is this design better? -

Thanks in advance :)


  Post Attachments 
SSC.JPG (9 views, 105.33 KB)
Post #1350826
Posted Tuesday, August 28, 2012 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
david_a_wallace (8/28/2012)
OK I have tried to take the advice given (thanks for giving it) and change the database table structure, I have linked the tables into MS Access and drawn the links in the MS Access diagram (it is not showing the 1 to many connections etc but I think they are obvious enough). Is this design better? -

Thanks in advance :)


That looks much better!!!

You have ContainerTypeID as a FK in your contracts table. I don't know the business model here but from looking at this it seems like the container is part of the contract, unless of course the contract is for a particular type of container. I would guess that you want to have ContainerID in the contract table instead of the type. One thing I would highly recommend is to not change the names of your columns from table to table. It will cause you pain down the road. For example you have SaleManagerID in the SalesManagers table but then you have SalesManager in the References table. I assume that is the SalesManagerID? This is good example of why the name should stay the same. In the ContainerMovements table you might consider changing MoveFromID to MoveFromLocationID. It isn't much more to type but it adds absolute clarity on what it is.

This is a huge improvement over what you had yesterday. Keep going in this direction and you will have a solid database. Everything you do to make your database structure better will make getting data out of it a lot less painful om the future.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350930
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse