We've moved a database from one SQL server to another using a backup
and restore, we had some problems because the location was slightly
different when we moved the database, then we had to create all the
users again.

We're now receiving an error when opening the client utility - which
is

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

Any suggestions?

database was working beforehand so i can't see why it would be an
error in the code

Regards

Paul

PS SQL2000 with SP3

Hi Paul,

That generally happens for one of three reasons:

1. You tried to uncheck the Allow Nulls checkbox in Enterprise Manager for a
column that does in fact contain nulls.

To get around this, open up QA and run an UPDATE statement on your table(s)
to initialize the column to a default value.  For example, on a bit column,
you might want to intialize it to 0 for False:

UPDATE myTable
SET myProblemColumn = 0
WHERE myProblemColumn IS NULL

Now you don't have nulls in that column, so you can go ahead and check that
Allow Nulls checkbox.  It's also a good idea to set a default for the column
when disallowing nulls.  For the above example, you would enter "(0)" in the
Default Value property of the column in EM.

2. You tried to add a relationship between two tables where the foreign key
value of the "detail" table could not be found in the "master" table.

To fix this, you need to first delete the records (or update their FK
column) that have the mismatch, such as:

-- Find the rascals...
SELECT *
FROM myDetailTable
WHERE myForeignKey NOT IN (
            SELECT myPrimaryKey
            FROM myMasterTable)
-- Get rid of 'em...
DELETE myDetailTable
WHERE myForeignKey NOT IN (
            SELECT myPrimaryKey
            FROM myMasterTable)

3. You're trying to make a column the primary key when it contains NULLs or
duplicate values.  The fix for this is much like #2:

-- Find the dups...
SELECT myPK
FROM myTable
GROUP BY myPK
HAVING COUNT(myPK) > 1
-- Delete them...
DELETE myTable
WHERE myPK IN (
            SELECT myPK
            FROM myTable
            GROUP BY myPK
            HAVING COUNT(myPK) > 1)
-- Find the NULLs...
SELECT *
FROM myTable
WHERE myPK IS NULL
-- Delete them...
DELETE myTable
WHERE myPK IS NULL

Hope this helps!

Eric



Show quoteHide quote

Memory leak in OleDbCommand.ExecuteNonQuery()?
C# and ADO.Net - Cheap Question!
ExecuteNonQuery returns ORA-01036 illegal variable name/number
how to connect Access database
Case-sensitivity in a stored procedure
VB.Net Joining Paradox and SQL Server Table?
(Q for David Sceppa!) Problems submitting hierarchical changes to DB
Parameter expected?
dataadapter and component
Filter Dataset