Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Nov 05, 2012 02:42 AM by wavemaster
Nov 04, 2012 03:03 AM|LINK
I messed up somewhere along the way and created a new table UserProfileREV. Pretty much the same as the old one wiht some extra columns.
Now, I need to redo the relationships between the tables and I get an error message when I try to create UserProfileREV to webpages_UsersInRoles. The error message is below.
At that moment I already have: Webpages_UsersInRoles RoleId to webpages_Roles for RoleId and webpages_Membership to UserProfileREV for UserId.
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlServerCe.SqlCeException: A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = FK_webpages_Membership_UserProfile ]
Here is the
MSDN page that explains the relationships for the starter site.
Nov 04, 2012 03:15 AM|LINK
Webmatrix allows to create relationship between tables..have u tried it???
Nov 04, 2012 03:18 AM|LINK
Yes... the error above comes when I created the relationship between UserProfileREV and webpages_UsersInRoles
Nov 04, 2012 03:22 AM|LINK
The above error says : corresponding primary key value does not exist.
Primary key value does not exist...UserId is a int value..have u made it IDENTITY for auto increment??
Nov 04, 2012 03:34 AM|LINK
It is the primary key and identity in UserProfileREV
It is the primary key in webpages_Membership
It is a primary key together with RoleId in webpages_UsersInRoles
Nov 04, 2012 01:09 PM|LINK
You say you created a new table. Was there data already in the Security tables? Did you copy data from the old table to the nw table?
I am assuming you are using the simple membership provider schema in which there should be a user profile row corresponding to each WebPages_Membership row, both sharing the same value for the primary key. If you replaced the UserProfile table with a new
UserProfileRev table and retained the data in the WebPages_Membership table, it could be that there are rows in the new UserProfileRev table that do not have the same value for the primary key as in the old UserProfile table.
The error is saying that it has tried to match the primary key of a record in the UserProfileRev table to a key in the membership table. And (at least one) case exists where the matching key cannot be found.
What are the key values in each of the affected tables?
Nov 04, 2012 01:14 PM|LINK
I have a few
screenshots here assembled in a pdf for you to look at.
Nov 04, 2012 01:30 PM|LINK
Problkem is with UsersInRoles (which has a compound primary key consisting of two columns)
(1) UserId in UsersInRoles shows the default value is NULL - bad! Any column that is (part of) a primary key must be non-null.
(2) The RoleId column is not shown in your document as (part of) the primary key and nulls are allowed on it - BAD.
The structure should be:
CREATE TABLE [webpages_UsersInRoles] (
[UserId] int NOT NULL
, [RoleId] int NOT NULL
ALTER TABLE [webpages_UsersInRoles] ADD CONSTRAINT [PK__webpages_UsersInRoles] PRIMARY KEY ([UserId],[RoleId]);
ALTER TABLE [webpages_UsersInRoles] ADD CONSTRAINT [fk_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [webpages_Roles]([RoleId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE [webpages_UsersInRoles] ADD CONSTRAINT [fk_UserId] FOREIGN KEY ([UserId]) REFERENCES [UserProfile]([UserId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
Nov 04, 2012 05:13 PM|LINK
Simple membership... yes.
FYI, this is SQL CE as part of WebMatrix.
NULL in Default Value is put there by WebMatrix, I think it is different from 0.
I have emptied out all tables, deleted all the relatioships, saved everything close the site and re-opened.
1st I create relationship between UserProfileRev and webpages_Membership OK
2nd I create the relationship between webpages_UsersInRoles and webpages_Roles OK
3rd I create relationship between UserProfileREV and webpages_UsersInRoles same error message:
The references table must have a primary or candidate key.
The referenced table must have a primary or candidate key. [ FK Name = FK_webpages_usersInRoles_UserProfileREV ]
System.Data.SqlServerCe.SqlCeException (0x80004005): The referenced table must have a primary or candidate key. [ FK Name = FK_webpages_usersInRoles_UserProfileREV ]
at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at Microsoft.WebMatrix.DatabaseManager.SqlCeDatabase.Helpers.UpdateRelationships(DbConnection connection, DbTransaction transaction, String schema, TableInfo tableInfo)
at Microsoft.WebMatrix.DatabaseManager.SqlCeDatabase.SqlCeDatabaseProvider.TryEditingTable(String connectionString, String schema, TableInfo tableInfo, Boolean& retry, Boolean forceUpdate)
at Microsoft.WebMatrix.DatabaseManager.SqlCeDatabase.SqlCeDatabaseProvider.EditTable(String connectionString, String schema, TableInfo tableInfo)
at Microsoft.WebMatrix.DatabaseManager.IisDbManagerModuleService.EditTable(DatabaseConnection databaseConnection, String schema, Object tableInfoData, String configPathState)
at Microsoft.WebMatrix.DatabaseManager.Client.ClientConnection.EditTable(String schema, Object tableInfoData)
Nov 04, 2012 11:34 PM|LINK
For a relationship to work in a relational world, the foreign key table must reference - in the promary key table - the key, the whole key, and nothing but the whole key (so help me Codd).
The primary key of the webpages_UsersInRoles table is a compound key consisting of userId AND role ID.
You must create a relationship between webpages_UsersInRoles and webpages_Membership (using userId as the common column).
You are trying to establish a foreign key using only half (userId) of the webpages_UsersInRoles primary key (userId+roleId). SQL Server will not permit this. It is telling you that you are using the wrong table (UserProfileRev ) as the "source" of the userId.
Think of it this way: assume userid 42 actually is in three roles (roleId1, roleId2, and roleId 4). A foreign key must point to 0 or one row of the referenced table. To which of user 42's three roles could it ever point? It really is illogical to say
"give me the single role for userId 42".
You are having problems with the Many-to-Many nature of the relationship between webpages_Membership and webpages_Roles: each webpages_Membership may be in zero, one, or more webpages_Roles and each webpages_Roles may apply to zero, one, or more webpages_Membership(s).
This M:N relationship is implemented by the webpages_UsersInRoles ("junction") table. UserProfileREV is simply not involved in that relationship (other than the fact that it has the same primary key as does webpages_Membership, making it, logically, a
"subtype" of webpages_Membership).
So, to summarize, if you want to tie a userId into the webpages_UsersInRoles , you do it via a foreign key relationship from webpages_UsersInRoles to webpages_Membership.