Can foreign key constraints be temporarily disabled using T-SQLin SQL Server.

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables

- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Also sometimes it is handy to disable all triggers as well,

sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

exec sp_msforeachtable @command1="print '?'",
@command2="ALTER TABLE ? ENABLE TRIGGER  all"

NHibernate and ‘Invalid Index N for this SqlParameterCollection with Count=N error’

If you have ever used NHibernate I am sure you have encounted the error ‘Invalid Index ‘N’ for this SqlParameterCollection with Count=N’ (where N is any number) exception when trying to create your mappings.

If you are completely new to NHibernate, as I am I am sure this one has you scratching your head saying…. WTF.

The good news is that ‘normally’ the solution to this problem is pretty easy, but before I tell you the answer let me explain the issue.

Take a look at the code below

view plaincopy to clipboardprint?

1. public EpisodeMap()
2. {
3. WithTable( "Episode" );
5. Id( x => x.ID ).GeneratedBy.Identity();
7. Map( x => x.LevelTypeID );
8. Map( x => x.Name );
9. Map( x => x.Description );
10. Map( x => x.EpisodeNumber );
11. Map( x => x.EpisodeDate );
12. Map( x => x.CreatedDate );
13. Map( x => x.Enabled );
15. References( x => x.EpisodeLevel )
16. .WithForeignKey( "LevelTypeID" ).TheColumnNameIs( "LevelTypeID" )
17. .Access.AsCamelCaseField( Prefix.Underscore )
18. .FetchType.Join();
20. }

Take notice to the mapping above. I have a many-to-one mapping for EpisodeLevel, but I have also created and mapped the FK to EpisodeLevel as LevelTypeID.

The issue (as i have experienced it) is this:

Because I have mapped my FK to the Episode Level table as .LevelTypeID as well as to the EpisodeLevel entity NH is going to try to create multiple associations on that field. However it cannot because that is not correct.

To solve this issue (mostly in my experience) all you need to do is remove the following line
Map( x => x.LevelTypeID );

If you MUST populate the LevelTypeID property at the top level, do so by providing the value as a pass through from Episode Level as such:

view plaincopy to clipboardprint?

1. public Int32 LevelTypeID
2. {
3. get { return EpisodeLevel.LevelTypeID; }
4. }

NHibernate and ‘Invalid Index N for this SqlParameterCollection with Count=N error’ is error related to NHibernate mapping so please check your mapping class mostly with composite keys and foreign keys.

I hope this helps someone.

Thanks,

Rajesh