How SQL Server adds a delete foreign key, a primary key, and updates the self-increment property

1. Add delete primary key and foreign key

For example:

-----Delete a PRIMARY KEY constraint
DECLARE @NAME SYSNAME
DECLARE @TB_NAME SYSNAME
SET @TB_NAME = ' Date '
SELECT TOP 1 @NAME =name from SYS. OBJECTS with (NOLOCK)
WHERE type_desc = ' primary_key_constraint ' and parent_object_id = (
SELECT object_id
From SYS. OBJECTS with (NOLOCK)
WHERE NAME = @TB_NAME)
SELECT @NAME as PK
DECLARE @ALTERSQL NVARCHAR (MAX)
SET @ALTERSQL =n ' ALTER TABLE ' [email protected]_name+ '
DROP CONSTRAINT ' [email protected]+ '
EXEC sp_executesql @ALTERSQL

----Add a PRIMARY KEY constraint
ALTER TABLE Date ADD constraint pk_date primary key (ID)

----The SQL statement that sets the FOREIGN KEY constraint:
ALTER TABLE student ADD constraint fk_student_classes foreign key (cla_id) references classes (ID)

----Delete a FOREIGN KEY constraint
ALTER TABLE student DROP constraint fk_student_classes

2. Update of the self-increment property

------If only the specified value is inserted, the following statement can be used to temporarily cancel

SET Identity_insert classes on
INSERT into classes (id,name) VALUES (7, ' Test 1 ')
SET Identity_insert [Classes] OFF

-----Add a column, delete the self-increment column, modify the column name

ALTER TABLE classes add id_temp int
Update a set Id_temp=id

ALTER TABLE classes drop column ID
exec sp_rename ' id_temp ', ' ID ', ' column '

--------by modifying the system's Column properties on the table, improper use of the method will likely cause other unpredictable errors (no successful operation)

sp_configure ' Allow update ', 1
Reconfigure with override
Go
Update syscolumns set colstat=0 where Colstat=1 and id=object_id (' tablename ')
Go
sp_configure ' allow update ', 0
Reconfigure with override

How SQL Server adds the delete foreign key, primary key, and update auto-increment property