none
ADO.Net miss applying unique value constaint in a VIEW doing an inner join on two tables RRS feed

  • Question

  • ADO.Net dataset is improperly applying database foreign key constraints on a view of two tables joined by an inner join.  What am I doing wrong?  How do I change the dataset configuration?

     

    I have two tables Users and Groups for my security.  The primary keys of both tables are GUIDs.  Both tables have a UserID and GroupID that are nvarchar, with secondary indexes on UserID and GroupID with unique value constraint.  (Each User belongs to one Group)

     

    I have a VIEW defined to inner join the Group table to the User table by GroupGUID.  Note this is all in SQL 2000 using Visual Basic.Net.  The VIEW looks great in Query Analyzer.

     

    But in ADO.Net the dataset created on the VIEW by the Data Source Wizard tries to apply the Unique GroupID constraint on the Group table to the view whenever two users are in the same Group. 

     

    Why is the dataset taking this constraint too far?

    How do I change the dataset in ADO.Net to correct this problem?

     

    Wednesday, November 7, 2007 2:58 AM

Answers

  • Right click on the column was not exactly the answer but it allowed me to see that one of the options other than Delete was Set Primary Key.  I clicked on the UserID column - right clicked - chose Set primary Key and boom that was it !!!!!

     

    Thank you so much for helping me through this, 3 months of trial and error and ignoring the problem for a while did not get me as far as one post and a few replies!

     

    Thank You!!!

    Kelly

     

    Tuesday, November 13, 2007 12:47 AM

All replies

  • Hi,

     

    ADO.net applies the constraints to it's own in-memory tables.  Since the content of the dataset's table is really the result of a view, the unique constraint does not work.  You just have to remove that constraint from the dataset.

    One thing to understand is that when you're dataset / database mapping is not 1-1, some database level constraints like this one do not need to be checked at the dataset level.  On the other hand this gives you the opportunity of adding higher level constraints to the dataset.

     

    Regards,

    Charles

    Wednesday, November 7, 2007 3:05 AM
  • How do I see the constraints in the dataset?

     

    How do I change them?

     

    Thanks for the quick response!

     

    Wednesday, November 7, 2007 4:14 AM
  • By the way the VIEW is defined in the database in SQL 2000.

     

    My dataset contains the User table, Group table and the VIEW User-Group.  So I am selecting all three database objects in the Data Source Wizard.

     

    Would having a dataset that only included the VIEW User-Group still have the problem on carring the GroupID unique value constraint too far?

     

    Thanks again

     

    Wednesday, November 7, 2007 4:20 AM
  • Hi,

     

    Is the constraint true for the view itself?

     

    Are you using the dataset designer? If so click on the GroupID column in the dataset's UserGroup table and make sure that the property Unique is set to the correct value (ie false to remove the uniqueness constraint).

     

    Regards,

    Charles

    Friday, November 9, 2007 12:11 AM
  • Since the view is on two tables, it shows both User-ID and Group-ID as key fields.  The dataset designer will let me remove the unique contraint on the UserID in the view, but when I try to do that for the GroupID it says that GroupID is the primary key for the view and will not let me change it.

     

    So why is ADO.Net choosing to setup the dataset in this way.  How can I have a choice?  Would listing the tables in a different order in the SQL for the inner join that creates the view make ADO.Net choose UserID as the primary key for the view.

     

    Here is the definition of the view:

     

    CREATE VIEW dbo.vwUserUserGroup
    AS
    SELECT     dbo.Users.UserID, dbo.Users.UserName, dbo.Users.Password, dbo.Users.AddDate, dbo.Users.AddBy, dbo.Users.ModDate, dbo.Users.ModBy,
                          dbo.UserGroups.GroupID, dbo.UserGroups.GroupName, dbo.UserGroups.AccessItem, dbo.UserGroups.AccessInventory,
                          dbo.UserGroups.AccessInvBook, dbo.UserGroups.AccessInvCycleCnt, dbo.UserGroups.AccessPackage, dbo.UserGroups.AccessUM,
                          dbo.UserGroups.AccessInvAcct, dbo.UserGroups.AccessInvDept, dbo.UserGroups.AccessStockRoom, dbo.UserGroups.AccessBIN,
                          dbo.UserGroups.AccessPO, dbo.UserGroups.AccessVendor, dbo.UserGroups.AccessFormula, dbo.UserGroups.AccessProcedure,
                          dbo.UserGroups.AccessFrmStatusType, dbo.UserGroups.AccessFrmStatus, dbo.UserGroups.AccessMachine, dbo.UserGroups.AccessMachineType,
                          dbo.UserGroups.AccessBatch, dbo.UserGroups.AccessUser, dbo.UserGroups.AccessUserGroup, dbo.UserGroups.AccessInstructions,
                          dbo.Users.Inactive
    FROM         dbo.UserGroups INNER JOIN
                          dbo.Users ON dbo.UserGroups.GroupGUID = dbo.Users.GroupGUID

     

     

    Monday, November 12, 2007 11:55 PM
  • Would an Order By clause hel ADO.Net make the right choice?

     

    Tuesday, November 13, 2007 12:01 AM
  •  

    Would an Order By clause hel ADO.Net make the right choice?

     

    I don't think so.  What about removing the primary keys?  They are not really useful for a 'virtual' table like this.  Right click on the column and select Delete Key.

     

    Charles

    Tuesday, November 13, 2007 12:32 AM
  • Right click on the column was not exactly the answer but it allowed me to see that one of the options other than Delete was Set Primary Key.  I clicked on the UserID column - right clicked - chose Set primary Key and boom that was it !!!!!

     

    Thank you so much for helping me through this, 3 months of trial and error and ignoring the problem for a while did not get me as far as one post and a few replies!

     

    Thank You!!!

    Kelly

     

    Tuesday, November 13, 2007 12:47 AM
  • I'm glad I could help.

     

    Regards,

    Charles

    Tuesday, November 13, 2007 12:52 AM