SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help With Foreign Keys


Help With Foreign Keys

Author
Message
david_a_wallace
david_a_wallace
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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 Smile
david_a_wallace
david_a_wallace
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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]
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26330 Visits: 17555
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.

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)
david_a_wallace
david_a_wallace
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26330 Visits: 17555
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.

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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26330 Visits: 17555
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.

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)
david_a_wallace
david_a_wallace
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26330 Visits: 17555
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.

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)
david_a_wallace
david_a_wallace
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
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 Smile
Attachments
SSC.JPG (14 views, 105.00 KB)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26330 Visits: 17555
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 Smile


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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search