Monday, May 30, 2011

Composite foreign key and order of columns

A simple database related thing which everybody knows.But I would like to post because this took around 4 hrs in our team.Let me come to the scenario. We have a database which has some primary and foreign keys.Its like a normal DB. Now we are doing some enhancements into it which brought a new column to all the primary keys .ie Composite primary key.

This introduces changes in all the other tables which refer using foreign keys.Since there are a number of tables in the database and all the table creation scripts were checked into TFS, we assigned some people to change the foreign key references.They were really new to project and they did their job in less time.We didn’t even run the scripts.But problems started when we run the changed table creation scripts.The error was

“There are no primary or candidate keys in the referenced table 'dbo.master' that match the referencing column list in the foreign key 'FK_Detail_Master'.”

For more details see the below scripts.

create table [master] (
Column1 int,
ColumnNew int
primary key(Column1,ColumnNew)
CREATE TABLE [dbo].[Detail](
Column1 [int] NULL,
ColumnNew [int] NULL,
[Column2] [nchar](10) NULL
ALTER TABLE [dbo].[Detail] WITH CHECK ADD CONSTRAINT [FK_Detail_Master] FOREIGN KEY([Column1], [ColumnNew])
REFERENCES [dbo].[master] ( [ColumnNew],[Column1])
ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [FK_Detail_Master]

In the first look its little difficult to identity the issue.But the issue is simple.The order of fields specified is wrong.The Column1 should come as follows.

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [FK_Detail_Master] FOREIGN KEY([Column1], [ColumnNew])
REFERENCES [dbo].[master] ([Column1], [ColumnNew])

It was a typo and may be overconfidence.But it costs a lot. We consulted with Dimitri Furman a Microsoft consultant for database and he confirmed the behavior.

Sunday, May 29, 2011

Notification on Network connectivity changes

Today I came to notice a small interesting portion in our project. Its nothing but an event handler which will execute when we connect or disconnect the network cable.According to the network state change the application had to show a Window to get some details. It’s a simple static event

System.Net.NetworkInformation.NetworkChange.NetworkAvailabilityChanged += new System.Net.NetworkInformation.NetworkAvailabilityChangedEventHandler(NetworkChange_NetworkAvailabilityChanged);
void NetworkChange_NetworkAvailabilityChanged(object sender, System.Net.NetworkInformation.NetworkAvailabilityEventArgs e)
//e.IsAvailable Give you the current status

Oh  forgot to tell about the project.It’s a .Net 3.5 WPF application which extensively uses WCF for communication.

Thursday, May 26, 2011

Merging files

Some time back we faced a simple situation. Its nothing but we need to merge a good number of files into one file. It was not a one time process. We may need to do it when ever any of the files change because those files are residing in the TFS source control.

We wanted to do it simply .ie without writing a .Net program.Normally developers have a tendency to write program even for small things .The first method was to use the copy command.Yes the same DOS command copy. We were able to do that as follows till the files grew beyond MBs.

Copy *.* c:\combined.sql

When the individual files grew bigger ie to MBs this command started appending some unwanted characters into the out put file. This prevent us from executing the sql files using SQL Server Management Studio.

Obviously the next solution was powershell.We got a script to combine files with in no time.Its simple,open source to modify,no need to compile but when we look at that it’s almost the same .net classes and code except some syntax changes Smile.

Uploaded the script here.I am not sure from which site I got this..