What are the SQL statements to delete table fields?

Recommended tutorial: "mysql video tutorial"

What are the SQL statements to delete table fields?

SQL statements to delete table fields are:

1. Delete columns without default values:

alter table Test drop COLUMN BazaarType

2. Delete columns with default values Column of value:

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

(alter table Test DROP COLUMN BazaarType prompts an error That is DF__SheetTest__Attac__0F8D3381)

Then delete the column alter table Test DROP COLUMN BazaarType

3.

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

Modify 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. Operations such as setting a field in the table to NOT NULL and adding a Default value to a field will add constraints to the field. After adding these constraints, use SQL scripts to modify the field type and delete the field. Similar errors will occur.

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 existing constraints

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

For example: exec('alter table T_tableName drop constraint The constraint name of the error message' )

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)

Recommended related articles: "sql database technology article"

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