Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

Posted on 2009-02-19
15
Medium Priority
?
1,176 Views
Last Modified: 2012-05-06
Using latest version of Visual Studio, MySQL (with MyDirect.NET)

Data Access Layer:  Created xsd dataset.  Viewing the query works fine.  (See query below...)

Business Logic Layer:  Utilizes this data...

Using db As New dsTrackingTableAdapters.ordersTableAdapter
     dtOrders = db.GetOrderContactsByProjectId(ProjectId)
End Using

I keep getting this error:  "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

I know it has to do with PK/FK but this table runs fine in the xsd dataset designer!
SELECT     '-1' AS TrackingId, '-1' AS TrackingTypeId, orders.ProjectId, orders.MyCompanyUserId, '-1' AS AttachmentTypeId, aspnet_membership.email, 
                      CONCAT(users.FirstName, ' ', users.LastName) AS ContactName, users.Company, users.ContactPhone, users.ContactFax, '' AS DeleteColumn, 
                      orders.OrderDateTime AS CreateDateTime, 'Purchased' AS TrackingType
FROM         orders INNER JOIN
                      users ON orders.MyCompanyUserId = users.MyCompanyUserId INNER JOIN
                      aspnet_users ON users.UserId = aspnet_users.userid INNER JOIN
                      aspnet_membership ON users.UserId = aspnet_membership.userid
WHERE     (orders.ProjectId = :ProjectId)

Open in new window

0
Comment
Question by:RobertNZana
  • 7
  • 7
15 Comments
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 23684917
You can disable constraints if you are having issues and you are sure you aren't getting duplicate records.  You are likely having an issue with your dataset not having a primary key defined, though.  If you do have a primary key, try setting your  EnforceConstraints property of your dataset to false.

myDataSet.EnforceConstraints = false
0
 
LVL 9

Author Comment

by:RobertNZana
ID: 23684959
How would I disable constraints for just a datatable (not dataset)?
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 23685005
I believe that the data table has an EnableConstraints property as well, set it the same way.  myTable.enableConstraints = False
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:RobertNZana
ID: 23685052
It doesnt.  In fact I tried creating a dataset, turning EnforceConstraints to false, then adding an empty new table to it, THEN filling the table and I still got the error.
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 23685080
It looks like your dataset is returning multiple instances.  Even if you can see the results in the designer, you can still have problems in production.

Is there a primary key designated in the data table?  Look at the data table in the designer and designate a unique ID for the data table if there isn't one currently.  (Some times the IDE has a hard time figuring out what field to use)
0
 
LVL 9

Author Comment

by:RobertNZana
ID: 23685122
My datatable is being filled by the select query.  Isn't duplicate rows ok (if that's what I want)?  No pk.
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 23685138
It is ok to an extent.  Primary keys do not only define duplication, they also determine which row should be updated when you run an update or delete.  The error is in place to prevent unintentional deleting and updating of records.
0
 
LVL 9

Author Comment

by:RobertNZana
ID: 23685221
Thanks. I know what PK/FK are used for.

I checked that enforceconstraints is false and it is.  Also, at the table level my constraints.count = 0.

Not sure why it's still giving me the error.  Is it because I'm joining other tables in my query?

Confusing...
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 23685418
An easy fix for this might be to just add the users.userid field into your select list.  
SELECT     users.userid, '-1' AS TrackingId, '-1' AS TrackingTypeId, orders.ProjectId, orders.MyCompanyUserId, '-1' AS AttachmentTypeId, aspnet_membership.email, 
                      CONCAT(users.FirstName, ' ', users.LastName) AS ContactName, users.Company, users.ContactPhone, users.ContactFax, '' AS DeleteColumn, 
                      orders.OrderDateTime AS CreateDateTime, 'Purchased' AS TrackingType
FROM         orders INNER JOIN
                      users ON orders.MyCompanyUserId = users.MyCompanyUserId INNER JOIN
                      aspnet_users ON users.UserId = aspnet_users.userid INNER JOIN
                      aspnet_membership ON users.UserId = aspnet_membership.userid
WHERE     (orders.ProjectId = :ProjectId)

Open in new window

0
 
LVL 9

Author Comment

by:RobertNZana
ID: 23685750
No, it still gives me the error.  This is an orders table so the same person might have multiple records.  This is such a confusing problem.
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 23686316
What is the unique identifier in the orders table?  Even with multiple orders per user, you should have unique ID for each order.  That should appear in your select list and it should automatically be assigned the primary key role.
0
 
LVL 9

Author Comment

by:RobertNZana
ID: 23686361
Even if I just want to do a SELECT statement?  That doesn't make sense.

Orderid is the pk.  But I shouldn't have to include that in the query.  
0
 
LVL 6

Accepted Solution

by:
matthewrhoades earned 375 total points
ID: 23686378
Because it is an XSD dataset, if you do not have it the dataset cannot determine the difference between one order and the next.  Include it in the query, there is no reason you have to display the field.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23784819
It was not a DB Related issue.

Check all the MaxLength property of the columns in your DataTable.
If you are using an .xsd file,  check the properties and make sure the AllowDBNull property is set to False.

The column that has column length specified less compared to the actual value might cause this problem.
0
 
LVL 9

Author Closing Comment

by:RobertNZana
ID: 31548917
I didn't really ever solve this problem. But thanks for your help...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question