How to modify properties of an existing column in SQL

You can modify the properties of an existing column by using the ALTER TABLE statement, including modifying the column type, size, adding or removing non-empty constraints, primary key constraints, foreign key constraints, and modifying column default values.

How to modify existing column properties in SQL

Preface
Modifying the properties of existing columns is often required in database management and can help optimize table structure and data accuracy.

Modify column type
Use the ALTER TABLE statement to modify the column type, the syntax is as follows:

 <code class="sql">ALTER TABLE table_name ALTER COLUMN column_name NEW_DATA_TYPE;</code>

For example, modify age column from SMALLINT to INT :

 <code class="sql">ALTER TABLE employees ALTER COLUMN age INT;</code>

Modify the column size
For some types (such as VARCHAR and CHAR ), you can modify the column size. Use the following syntax:

 <code class="sql">ALTER TABLE table_name ALTER COLUMN column_name SET DATA_TYPE(NEW_SIZE);</code>

For example, modify the size of name column from 20 to 50:

 <code class="sql">ALTER TABLE employees ALTER COLUMN name SET VARCHAR(50);</code>

Add or remove non-empty constraints
Non-empty constraints force columns to have values ​​when inserted or updated. Use NOT NULL or NULL constraints to add or remove it:

 <code class="sql">ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL; ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;</code>

Add or delete primary key constraints
Primary key constraints ensure that the column values ​​are unique. Add or remove it using the following syntax:

 <code class="sql">ALTER TABLE table_name ADD PRIMARY KEY (column_name); ALTER TABLE table_name DROP PRIMARY KEY;</code>

Add or delete foreign key constraints
Foreign key constraints force column values ​​to exist in another table. Add or remove it using the following syntax:

 <code class="sql">ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(other_column_name); ALTER TABLE table_name DROP FOREIGN KEY column_name;</code>

Modify the default value of the column
Use the DEFAULT clause to modify the default value of the column, the syntax is as follows:

 <code class="sql">ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;</code>

For example, set the default value of age column to 18:

 <code class="sql">ALTER TABLE employees ALTER COLUMN age SET DEFAULT 18;</code>

The above is the detailed content of How to modify properties of an existing column in SQL. For more information, please follow other related articles on the PHP Chinese website!