August 27, 2012 at 9:40 am
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 🙂
August 27, 2012 at 9:47 am
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]
August 27, 2012 at 10:02 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 10:06 am
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?
August 27, 2012 at 10:38 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 10:48 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 3:00 pm
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?
August 27, 2012 at 3:14 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2012 at 5:14 am
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 🙂
August 28, 2012 at 7:39 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2012 at 8:40 am
Thanks for your help, I'll keep on with this method of making numeric autonumber IDs instead of using things like people's names.
The reason the ContainerTypeID is in the contact different types of containers (by type I mean category) have different rental rates. If they wanted a 13ft container it would have different rate to a 10ft container for example. It wouldn't matter (for billing purposes at least) which container it was, only the category (type) of container would matter.
Thanks again for the help 🙂
August 28, 2012 at 8:58 am
david_a_wallace (8/28/2012)
Thanks for your help, I'll keep on with this method of making numeric autonumber IDs instead of using things like people's names.The reason the ContainerTypeID is in the contact different types of containers (by type I mean category) have different rental rates. If they wanted a 13ft container it would have different rate to a 10ft container for example. It wouldn't matter (for billing purposes at least) which container it was, only the category (type) of container would matter.
Thanks again for the help 🙂
That makes sense. Keep in mind that if you tracked the actual container you not only have access to the details about the specific container but you also have access to the type. The way your structure is right now you have no idea which container belongs to a contract and you can't tell if a given container is currently under contract.
If you can't tie those back you are going to have a difficult time with things like... "Customer X has not paid their bill, we need to empty their container and auction off the stuff inside". With your current structure you have no idea which container that is. You can narrow it down by type but that is it. Remember that things like a container type is going to be static. A container does not change type or sizes over time (unless it was entered incorrectly). A 10ft container is not likely to become a 13ft container. 😛
In other words, the contract is for a container not a type of container. Now you do need to know the type of container to establish the contract initially but that should be something like "show me all empty containers of type X". Then you add one to the contract. It happens to be ContainerID 1234. ContainerID 1234 has a container type of 13ft or whatever. Now in 6 months when they stop paying the bill you know exactly which container has been contracted and by the containertype you also know what size it is.
Consider something like there was a fire in a 13ft container and you have 100 of them. With your current structure you don't even know which one it was. If this is a storage business are you going to call 100 customers with that container type and tell them there was a fire but you aren't sure if it was their stuff that burned? If you know which container belongs to the contract you can call the 1 customer affected.
The police show up with a search warrant for a certain customers container...
There is a crash on the highway and one of the portable containers is identified to belong to you. Who was renting this one???
Hope that makes sense?!?!?!?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply