How can I remove the default restrictions in my particular case?

Question

SQL

I create a table and add data. Then I need to remove restrictions from this table and add new ones.

CREATE TABLE dbo.CONTENT_TFS (
  TFS_ID BIGINT NOT NULL,
  TFS_CONTENT NVARCHAR(1024) NOT NULL,
  PRIMARY KEY CLUSTERED (TFS_ID)
) ON [PRIMARY]
GO

INSERT INTO dbo.CONTENT_TFS (TFS_ID, TFS_CONTENT)
  VALUES 
  (1, '111'),
  (2, '222')
GO

Next, I need to add a column and set a constraint. That is, make a composite key from TFS_ID, SERVER_ID

ALTER TABLE dbo.CONTENT_TFS 
  ADD SERVER_ID INT NOT NULL DEFAULT (-1); -- -1, как не заданный идентификатор
GO

---- Удаляем ограничение первичного ключа.
--ALTER TABLE dbo.CONTENT_TFS 
--  DROP CONSTRAINT 
--GO

ALTER TABLE dbo.CONTENT_TFS
  ADD CONSTRAINT PK_CONTENT_TFS PRIMARY KEY NONCLUSTERED (TFS_ID, SERVER_ID) 
GO

As a result, I want to get a table that would have turned out if I had created it like this (the only thing is that the restrictions will already be named, don’t pay attention to this, here I just brought the final table so that it’s clear where I’m going):

CREATE TABLE dbo.CONTENT_TFS (
  TFS_ID BIGINT,
  TFS_CONTENT NVARCHAR(1024) NOT NULL, 
  SERVER_ID INT NOT NULL,
  PRIMARY KEY NONCLUSTERED (TFS_ID, SERVER_ID)
)

Found such options, but apparently I'm doing something wrong.

DECLARE @constraint sysname
SELECT @constraint = object_name(default_object_id) 
  FROM sys.columns
  WHERE object_id = object_id('[dbo].[CONTENT_TFS]') AND name = 'TFS_ID'
-- Add additional criteria here if required
IF len(@constraint) > 0
  BEGIN
    EXEC ('ALTER TABLE [dbo].[CONTENT_TFS] DROP CONSTRAINT ' + @constraint)
  END
ELSE
  BEGIN
    Print ('No CONSTRAINT found')
  END


DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname =
  (
    SELECT name
    FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
    WHERE object_name(so.parent_obj) = 'CONTENT_TFS' AND so.xtype = 'D'
    AND sc.colid = (
      SELECT colid FROM syscolumns WHERE id = object_id('dbo.CONTENT_TFS') AND name = 'TFS_ID')
  )
SET @cmd = 'ALTER TABLE dbo.CONTENT_TFS DROP CONSTRAINT ' + @defname
EXEC(@cmd)

How to remove default restrictions which are unnamed.

Answer 1

I understand it is necessary to bang PK?

SELECT * FROM dbo.sysobjects WHERE xtype = 'PK' and parent_obj = OBJECT_ID('CONTENT_TFS')