Monday, 28 March 2011

JOINS - Which is which!?

Amazing post about different joins represented by a Venn Diagram.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Monday, 14 March 2011

T-SQL: How to rename a table or column using T-SQL in Microsoft SQL

So yeah, you could use the Microsoft SQL Server Management Studio UI to rename your table or column. But sometimes you need to do the rename in T-SQL. Here’s how.

How to rename a table:


EXEC sp_rename 'OldTableName','NewTableName'


How to rename a column:

EXEC sp_rename
@objname = 'TableName.OldColumnName',
@newname = 'NewColumnName',
@objtype = 'COLUMN'


For a more detailed explanation of sp_rename check out this MSDN article: http://msdn2.microsoft.com/en-us/library/ms188351.aspx

Wednesday, 9 March 2011

LinqToSql: ForeignKeyReferenceAlreadyHasValueException

I recently had an error with a project while attempting to modify a foreign key for a database object. Quite simply, once the foreign key was established, if any chances were made to it, it would throw this exception. This is correct by design! So in theory, the initial insert is fine, but any update made will throw this error.

The resolve this issue, open up the linq designer file (dbml) and modify the association between the table and its foreign counterpart. If you set 'Child Property' to true, this will allow you to add this association as a property to the database object for your class.

So in the code, instead of editing the foreign key

i.e. Object.ForeignKeyID = id;

You need to make a call using the same data context to retrieve the object, and set it to the property you have just created for your assoication.

i.e. Object.AssociationName = dataContext.Table.Single(val = val.id == idhere);