Changing column constraint null/not null = rowguid replication error

sql-server sql-server-2005 constraints ssms merge-replication
Dale picture Dale · Dec 16, 2009 · Viewed 9k times · Source

I have a database running under Sql server 2005 with merge replication. I want to change some of the FK columns to be 'not null' as they should always have a value. SQL server won't let me do that though, this is what it says:

  • Unable to modify table. It is invalid to drop the default constraint on the rowguid column that is used by merge replication. The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message. The transaction ended in the trigger. The batch has been aborted.

I am not trying to change the constraints on the rowguid column at all, only on another column that is acting as a FK. Other columns I want to set to be not null because the record doesn't make any sense without that information (i.e. on a customer, the customer name).

Questions: Is there a way to update columns to be 'not null' without turning off replication then turning it back on again? Is this even the best way to do this - should I be using a constraint instead?

Answer

Dale picture Dale · Dec 16, 2009

Apparently SSMS makes changes to tables by dropping them and recreating them. So just needed to make the changes using T-SQL statement.

ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn nvarchar(50) NOT NULL

Related questions

Turn off constraints temporarily (MS SQL)

I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships). I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in proper order (to …

Read More
How can I create a SQL unique constraint based on 2 columns?

I have a Table like this one: |UserId | ContactID | ContactName --------------------------------------- | 12456 | Ax759 | Joe Smith | 12456 | Ax760 | Mary Smith | 12458 | Ax739 | Carl Lewis | 12460 | Ax759 | Chuck Norris | 12460 | Bx759 | Bruce Lee I need to add a constraint to this table so that no user can …

Read More
Can there be constraints with the same name in a DB?

This is a follow-on question from the one I asked here. Can constraints in a DB have the same name? Say I have: CREATE TABLE Employer ( EmployerCode VARCHAR(20) PRIMARY KEY, Address VARCHAR(100) NULL ) CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, …

Read More