search

DNNCommunity / DNN.Forum

DNN.Forum
9 stars 7 forks source link

There are no primary or candidate keys in the referenced table 'Forum_Users' #35

Open Saeedalhs opened 2 years ago

Saeedalhs commented 2 years ago

Upgrading Forum module from ver5.0.3 to ver05.01.00 in DNN ver8.0.4.226 produces the following error.

SQL File: 05.00.07.SqlDataProvider System.Data.SqlClient.SqlException (0x80131904): There are no primary or candidate keys in the referenced table 'Forum_Users' that match the referencing column list in the foreign key 'FK_Forum_Posts_Forum_Users'. Could not create constraint or index. See previous errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:f1e16aa6-0947-4fec-a4fc-b283d9ef750d Error Number:1776,State:0,Class:16 if not exists (select from dbo.sysobjects where id = object_id(N'FK_Forum_Posts_Forum_Threads')) ALTER TABLE Forum_Posts ADD CONSTRAINT FK_Forum_Posts_Forum_Threads FOREIGN KEY ( [ThreadID] ) REFERENCES Forum_Threads ( [ThreadID] ) ON DELETE CASCADE , CONSTRAINT FK_Forum_Posts_Forum_Users FOREIGN KEY ( [UserID] ) REFERENCES Forum_Users ( [UserID] ) ON DELETE CASCADE System.Data.SqlClient.SqlException (0x80131904): There are no primary or candidate keys in the referenced table 'Forum_Users' that match the referencing column list in the foreign key 'FK_Forum_ThreadRating_Forum_Users'. Could not create constraint or index. See previous errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:f1e16aa6-0947-4fec-a4fc-b283d9ef750d Error Number:1776,State:0,Class:16 if not exists (select from dbo.sysobjects where id = object_id(N'FK_Forum_ThreadRating_Forum_Threads')) ALTER TABLE Forum_ThreadRating ADD CONSTRAINT FK_Forum_ThreadRating_Forum_Threads FOREIGN KEY ( [ThreadID] ) REFERENCES Forum_Threads ( [ThreadID] ) ON DELETE CASCADE , CONSTRAINT FK_Forum_ThreadRating_Forum_Users FOREIGN KEY ( [UserID] ) REFERENCES Forum_Users ( [UserID] ) System.Data.SqlClient.SqlException (0x80131904): There are no primary or candidate keys in the referenced table 'Forum_Users' that match the referencing column list in the foreign key 'FK_Forum_ThreadsRead_Forum_Users'. Could not create constraint or index. See previous errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:f1e16aa6-0947-4fec-a4fc-b283d9ef750d Error Number:1776,State:0,Class:16 if not exists (select from dbo.sysobjects where id = object_id(N'FK_Forum_ThreadsRead_Forum_Forums')) ALTER TABLE Forum_ThreadsRead ADD CONSTRAINT FK_Forum_ThreadsRead_Forum_Forums FOREIGN KEY ( [ForumID] ) REFERENCES Forum_Forums ( [ForumID] ) ON DELETE CASCADE , CONSTRAINT FK_Forum_ThreadsRead_Forum_Users FOREIGN KEY ( [UserID] ) REFERENCES Forum_Users ( [UserID] ) System.Data.SqlClient.SqlException (0x80131904): There are no primary or candidate keys in the referenced table 'Forum_Users' that match the referencing column list in the foreign key 'FK_Forum_TrackedForums_Forum_Users'. Could not create constraint or index. See previous errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:f1e16aa6-0947-4fec-a4fc-b283d9ef750d Error Number:1776,State:0,Class:16 if not exists (select from dbo.sysobjects where id = object_id(N'FK_Forum_TrackedForums_Forum_Forums')) ALTER TABLE Forum_TrackedForums ADD CONSTRAINT FK_Forum_TrackedForums_Forum_Forums FOREIGN KEY ( [ForumID] ) REFERENCES Forum_Forums ( [ForumID] ) ON DELETE CASCADE , CONSTRAINT FK_Forum_TrackedForums_Forum_Users FOREIGN KEY ( [UserID] ) REFERENCES Forum_Users ( [UserID] ) System.Data.SqlClient.SqlException (0x80131904): There are no primary or candidate keys in the referenced table 'Forum_Users' that match the referencing column list in the foreign key 'FK_Forum_TrackedThreads_Forum_Users'. Could not create constraint or index. See previous errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:f1e16aa6-0947-4fec-a4fc-b283d9ef750d Error Number:1776,State:0,Class:16 if not exists (select * from dbo.sysobjects where id = object_id(N'FK_Forum_TrackedThreads_Forum_Threads')) ALTER TABLE Forum_TrackedThreads ADD CONSTRAINT FK_Forum_TrackedThreads_Forum_Threads FOREIGN KEY ( [ThreadID] ) REFERENCES Forum_Threads ( [ThreadID] ) ON DELETE CASCADE , CONSTRAINT FK_Forum_TrackedThreads_Forum_Users FOREIGN KEY ( [UserID] ) REFERENCES Forum_Users ( [UserID] )

Saeedalhs commented 2 years ago

For now it's been resolved by manually defining the primary key below:

ALTER TABLE dbo.Forum_Users ADD CONSTRAINT PK_Forum_Users PRIMARY KEY CLUSTERED ( UserID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO ALTER TABLE dbo.Forum_Users SET (LOCK_ESCALATION = TABLE) GO