Shulou(Shulou.com)05/31 Report--

This article mainly introduces the sql statements that delete the fields in the table in the database. It is very detailed and has a certain reference value. Friends who are interested must finish reading it.

The sql statements to delete fields in the table are: 1, delete columns without default values [alter table Test drop COLUMN BazaarType]; 2, delete columns with default values, [alter table Test DROP COLUMN BazaarType].

Delete a sql statement for a field in a table

1. Delete columns that do not have default values:

Alter table Test drop COLUMN BazaarType

two。 Delete the column with default values:

Delete the constraint first (default)

Alter table Test DROP CONSTRAINT DF__Test__BazaarType__3C4ACB5F

(alter table Test DROP COLUMN BazaarType prompt error report is DF__SheetTest__Attac__0F8D3381)

Then delete the column

Alter table Test DROP COLUMN BazaarType

3. Modify field name

ALTER TABLE table name ADD field name INT DEFAULT (0) NOT NULL

Modify primary key field type

Alter table [tablename] alter column [colname] [newDataType])

When you modify the field type of a table, an error is reported because of constraints.

a. Operations such as setting the field in the table to NOT NULL (not empty) and adding the default value (default) to the field will add constraints to the field. After adding these constraints, similar errors will occur when using the SQL script to modify the field type and delete the field.

b. Find the existing constraint on the field and delete the existing constraint.

c. Just execute the modify / delete script again.

Solution:

1. Find out the constraint name of this field in the table (or according to the existing prompts and objects' DF__*')

Declare @ name varchar (50) select @ name = b.name from sysobjects b join syscolumns an on b.id = a.cdefault where a.id = object_id ('TableName') and a.name =' ColumName'

two。 Delete existing constraints

Exec ('alter table TableName drop constraint' + @ name)

For example:

Exec ('constraint name of alter table T_tableName drop constraint error message')

3. Then execute the script that modifies the field type

Alter table dbo.T_tableName alter column Id BIGINT not NULLalter table dbo.T_tableName add constraint PK_Id primary key (Id) above are all the contents of this article entitled "what are the sql statements for deleting fields from a table in the database?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.