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

Editor to share with you the database to delete table fields of the sql statements, I believe that most people do not know much, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

What are the sql statements that delete table fields?

The sql statements that delete table fields are:

1. Delete columns that do not have default values:

Alter table Test drop COLUMN BazaarType

two。 Delete the column with default values:

Delete constraint (default) alter table Test DROP CONSTRAINT DF__Test__BazaarType__3C4ACB5F first

(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.

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 table fields in the database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.