SQL Server modifies data columns

modifying data columns

In the development and production process, the spelling error of the column name or the change of the column name is required to manipulate the data table, in most cases it is not necessary to modify.

There are several scenarios in which we cannot directly modify the data columns:

1. The column for the index.
2. Columns for CHECK, FOREIGN key, UNIQUE, or PRIMARY key constraints.
3. The column associated with the default value (defined by the Defaults keyword), or the column bound to the default object.
4. The column bound to the rule.

modifying columns using the SSMS database management tool

1, connect the database, open the database to be modified, select Data table-Right click-"Select Design."

2, in the newly opened window view-"Click on the line to modify-" can modify the column name, column type, whether it is empty, attributes, etc.-"Modify the Click save button (or Ctrl+s).

Modifying column names by using T-SQL scripts to modify columns

Syntax: EXEC sp_rename indicates that the old column name, the new column name;

Example: Exec sp_rename ' [test1].height6 ', ' height7 ';

modifying column data types

Syntax: ALTER TABLE database name. dbo. Table name alter COLUMN name TYPE [constraint];

Example: ALTER TABLE [TESTSS].DBO. [TEST1] ALTER column HEIGHT7 int null;

Modify whether a column is nullable

Syntax: ALTER TABLE database name. dbo. Table name ALTER column name type constraint;

Example: ALTER TABLE [TESTSS].DBO. [TEST1] ALTER column HEIGHT7 int NOT null;

Modify column default values

If the default value does not exist

Syntax: ALTER TABLE database name. dbo. Table name add constraint constraint name default value for column name;

Example: ALTER TABLE [TESTSS].DBO. [Test1] Add constraint df_h default 1 for HEIGHT7;

If the default value exists

Grammar:

--The first step is to determine whether the default value is present, delete if it exists, do not delete if it does not exist
if exists (SELECT * from sys.check_constraints where object_id = object_id (default constraint name) and parent_object_id = object_id (table name))
ALTER TABLE name DROP constraint default value constraint name

--Second step to add default values

ALTER TABLE name ADD CONSTRAINT constraint name default value for column name;

Example:

--The first step is to determine if the constraint exists, delete if it exists, do not delete if it does not exist
if exists (SELECT * from sys.check_constraints where object_id = object_id (' Df_h ') and parent_object_id = object_id (' [Test Ss].dbo. [Test1])
ALTER TABLE [TESTSS].DBO. [Test1] Drop constraint df_h

--Second step to add default values

ALTER TABLE [TESTSS].DBO. [Test1] Add constraint df_h default 2 for HEIGHT7;

Summarize

In the development or production database, once the data column is built, do not easily change, random changes may cause the database cascade operation failure and code errors.

SQL Server modifies data columns