What are the SQL statements to delete fields in a table?

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

SQL statement to delete fields in the table

1. Delete columns without default values:

alter table Test drop COLUMN BazaarType

2. Delete the column with the default value:

Delete the constraint (default value) first

alter table Test DROP CONSTRAINT DF__Test__BazaarType__3C4ACB5F

(alter table Test DROP COLUMN BazaarType The error message is DF__SheetTest__Attac__0F8D3381)

Then delete the column

alter table Test DROP COLUMN BazaarType

3. Modify the field name

ALTER TABLE 表名 ADD 字段名 INT  DEFAULT (0) NOT NULL；

Modify the primary key field type

 alter table [tablename] alter column [colname] [newDataType]）

When modifying the field type of a table, an error will be reported because there are constraints.

a. Setting the field in the table to NOT NULL (not empty) and adding a Default value (default value) to the field will add constraints to the field. , after adding these constraints, similar errors will occur when using SQL scripts to modify field types or delete fields.

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

c. Execute the modify/delete script again.

Solution:

1. Find the constraint name of the field in the table (or based on the existing prompt and object 'DF__******' )

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

2. Delete the existing constraints

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

For example:

exec('alter table T_tableName drop constraint  报错信息的约束名' )

3. Then execute the script to modify the field type

alter table dbo.T_tableName alter column Id BIGINT not NULL
alter table dbo.T_tableName add constraint PK_Id primary key(Id)

Related learning recommendations: SQL video tutorial

The above is the detailed content of What are the SQL statements to delete fields in a table?. For more information, please follow other related articles on the PHP Chinese website!