Last Updated:

Modify tables in SQL Server using ALTER TABLE command include  ( MySQL )

The ALTER TABLE command is used to make changes to the table:

  • Add columns
  • Delete columns
  • Modification of columns
  • Table name changes
  • Table encoding changes
  • Add or remove restrictions
 

For further examples, we will use the books table from the Bookstore database, which we created in one of the previous posts.

To view changes to columns in a table, use the command:
SHOW COLUMNS FROM table_name;

 

Before starting work, we will choose the database with which we will work.

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > USE Bookstore;

 

1USEBookstore;

How do I add a blade to a MySQL table?

 

The syntax for adding a column to a table is:

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE table_name ADD column_name data_type attributes;

 

1ALTERTABLEtable_nameADDcolumn_namedata_typeattributes;

For example, let's add a column to the books table named discount, which will store the discount percentage on books and the amount for storing the number of books.

You can add, remove, and modify multiple columns at once.

 
<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE books ADD discount TINYINT UNSIGNED, ADD amount SMALLINT UNSIGNED;

 

1ALTERTABLEbooksADDdiscountTINYINTUNSIGNED,ADDamountSMALLINTUNSIGNED;

Each new column is added to the end of the table. If you want to add a new column after a specific column, use the AFTER command.

Let's add a column shelf_position immediately after the price column.

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE books ADD shelf_position VARCHAR(20) AFTER Price;

 

1ALTERTABLEbooksADDshelf_positionVARCHAR(20)AFTERPrice;

How do I delete a blade from a MySQL table?

Syntax for removing a column from a table:

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE table_name DROP column_name;
1ALTERTABLEtable_nameDROPcolumn_name;

If there is only one column left in the table, it cannot be deleted.

Let's remove the edition column with the edition number of the book.

 

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE books DROP edition;

 

1ALTERTABLEbooksDROPedition;

How do I rearrange columns in a MySQL table?

To rearrange the blade use the AFTER command, you will also need to redefine the data type.

<текстареа class=»crayon-plain print-no» data-settings=»dblclick» readonly=»» style=»-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;» > ALTER TABLE table_name MODIFY COLUMN column_name data_type AFTER column_after_name;

1ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namedata_typeAFTERcolumn_after_name;

Let's move the price column to a place after the Author column:

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE books MODIFY COLUMN Price DECIMAL(15,2) AFTER Author;

 

1ALTERTABLEbooksMODIFYCOLUMNPriceDECIMAL(15,2)AFTERAuthor;

How do I change the name and data type of a column in a MySQL table?

 

The syntax for changing the name and data type of the column is:

 

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_data_type;

 

1ALTERTABLEtable_nameCHANGECOLUMNold_column_namenew_column_namenew_data_type;

Let's change the name of the column c reation_date to entry_date and the data type c TIMESTAMP to DATE.

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE books CHANGE COLUMN creation_date entry_date DATE;

 

1ALTERTABLEbooksCHANGECOLUMNcreation_dateentry_dateDATE;

If you want to change only the data type, leaving the column name the same, then use the following syntax:

 

<textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;" > ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

 

1ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namenew_data_type;

Modifying Tables in Microsoft SQL Server in T-SQL (ALTER TABLE)

 

Now let me show you how to change tables in Microsoft SQL Server to T-SQL. All changes to the tables are made by using the ALTER TABLE statement. First, let's look at the simplified syntax of the ALTER TABLE statement so that you have a better understanding of the structure of those queries, which we will consider later in the examples.

Simplified ALTER TABLE Statement Syntax

 

ALTER TABLE [Table Name] [Modification Type] [Column Name] [Data Type] [Nullable]

Add a new column to a table in T-SQL

To add a new column to the table, we write an ALTER TABLE statement with the ADD parameter, specify the name of the new column (in our case, ProductDescription, i.e. product description), its data type and the ability to accept a null value (as noted earlier, if there are rows in the table, then the column must first accept null values).

ALTER TABLE Goods ADD ProductDescription VARCHAR(300) NULL; GO SELECT * FROM Goods;

Alter table my sql

Delete a column from a table in T-SQL

If you do not need a column, then it is easy to delete it (if it does not participate in any links) with the DROP COLUMN parameter, for example, we changed our mind about adding a new column with a description of the product, and to remove it, write the following instruction.

ALTER TABLE Goods DROP COLUMN ProductDescription; GO SELECT * FROM Goods;

Alter table my sql

Set the NOT NULL property for a column on T-SQL

If you need to make a column mandatory, i.e. set the NOT NULL property for the column, then you need to use the ALTER COLUMN parameter, but be sure to remember that all rows in the column must already be filled, i.e. there are no NULL values.

Suppose in our case the price has become mandatory, in order to implement this in our table, write the following statement (just specify all the actual parameters of the column and change the one that is needed, in this particular case the ability to accept the null value).

 

ALTER TABLE Goods ALTER COLUMN Price MONEY NOT NULL;

Change the data type of a column to T-SQL

To change the data type of a column, in the same way, list all the parameters of the column with a change in the desired one, i.e. specify a new data type.

Let's say we need to increase the length of the string to store the name of the product (for example, up to 200 characters).

ALTER TABLE Goods ALTER COLUMN ProductName VARCHAR(200) NOT NULL;

Add a new column

For example, let's add a new column Address to the Customers table:

ALTERTABLECustomersADDAddressNVARCHAR(50)NULL;

In the example above, the Address column is of type NVARCHAR, plus a NULL attribute is defined for it. If data already exists in the table, the ALTER TABLE command will fail. However, if you want to add a column that should not accept NULL values, you can set a default value using the DEFAULT attribute:

ALTERTABLECustomersADDAddressNVARCHAR(50)NOTNULLDEFAULT'UNKNOWN';

Then, if data exists in the table, the column Address value is added to "Unknown".

Delete a column

You can now delete the column Address:

ALTERTABLECustomersDROPCOLUMNAddress;

Change type

Let's continue manipulating the Customers table: now let's change the data type of the FirstName column to NVARCHAR(200).

 

ALTERTABLECustomersALTERCOLUMNFirstNameNVARCHAR(200);

Adding CHECK constraints

If you add constraints, SQL Server automatically checks the existing data to ensure that it matches the constraints that you add. In case of non-compliance, they will not be added. Let's limit Age by age.

ALTERTABLECustomersADDCHECK(Age>21);

If there are rows in the table with values that do not meet the constraint, the sql command will not execute. If you want to avoid validation and add a constraint anyway, use the WITH NOCHECK expression:

ALTERTABLECustomersWITHNOCHECKADDCHECK(Age>21);

By default, the WITH CHECK value is applied, which checks for compliance with the restrictions.

Adding a Foreign Key

Let's imagine that initially 2 tables will be added to the database, which are not related to each other:

create table of customers

Now let's add a foreign key constraint to the CustomerId column (Orders table):

ALTERTABLEOrdersADDFOREIGNKEY(CustomerId)REFERENCESCustomers(Id);

Adding the Primary Key

Using the Orders table defined above, you can add a primary key to it for the Id column:

ALTERTABLEOrdersADDPRIMARYKEY(Id);

Adding Naming Restrictions

When adding constraints, you can specify a name for them - the CONSTRAINT operator (the name is written after it) is useful for this):

alter table orders

Removing restrictions

To remove restrictions, you must know their name. If there is a problem with this, you can always determine the name by using SQL Server Management Studio:

removing restrictions

You should expand the table node in the Keys subnode, where the names of the constraints for foreign keys are located (the names begin with "FK"). You can detect all DEFAULT constraints (names begin with "DF") and CHECK in the Constraints sub-node.

From the screenshot you can see that in this situation the name of the foreign key constraint (Orders table) is called "FK_Orders_To_Customers". Here, to remove the external, the following expression is suitable:

ALTERTABLEOrdersDROPFK_Orders_To_Customers;

Команда ALTER TABLE

The ALTER TABLE command modifies the table definition in one of the following ways:

  • adds a column
  • adds an integrity constraint
  • overrides the column (data type, size, silent value)
  • deletes a column
  • modifies memory characteristics or other parameters
  • Enables, disables, or removes an integrity constraint or trigger.
 
 

Condition: The table must be in the user's schema, or the user must have the ALTER ANY TABLE system privilege.

When adding a column with a NOT NULL constraint, the developer or database administrator must take into account a number of circumstances. First, you need to create a column without restriction, and then enter values in all its rows. After all the values in a column are non-null, you can apply a NOT NULL constraint to the column. If a user tries to add a column with a NOT NULL constraint, an error message is returned stating that either the table must be empty, or the column must contain values for each existing row (recall that after imposing a NOT NULL constraint on the column, it cannot contain NULL values in any of the existing rows). In Oracle, starting with version 8i, you can delete columns.

When changing the data types of existing columns or adding columns to a database table, a number of conditions must be met. It is generally accepted that an increase is good, and a decrease, as a rule, is not very good. Allowable magnifications:

  • Increase the size of a CHAR or VARCHAR2 column
  • Increase the size of the NUMBER column
  • Add new columns to a table

Reducing various characteristics of a table, including some types of column data and the actual number of columns in the table, requires special actions. Often, before you make a change, you need to make sure that all values in the corresponding column or columns are NULL values. To perform such operations on the columns of a table that contain data, the developer must find or create some kind of area for temporary storage of this data. For example, you can create a table by using the CREATE TABLE AS SELECT command that retrieves data from the primary key and the column or columns that are being modified. Valid changes are:

    • Reduce the size of the NUMBER column (only if the column is empty for all rows)
    • Reduce the size of the CHAR or VARCHAR2 column (only if the column is empty for all rows)
 
  • Change the data type of a column (only if the column is empty for all rows)

ALTER TABLE Example 1

To add a column to a table:

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Example 2

To resize a table column:

ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Example 3

To delete a table column:

ALTER TABLE t1 DROP COLUMN pole1;

Using the ALTER TABLE command, you can change the name of the table without actually transferring the physical information to the database:

ALTER TABLE t1 RENAME TO t2;

A similar operation can be performed using the RENAME command:

RENAME t1 TO t2;

You can modify the integrity constraints of database columns and tables, as well as deny, allow, and delete. This gives the developer the ability to create, modify, and delete business rules that restrict data. Consider adding constraints to the database. The simplicity or complexity of this process depends on certain circumstances. If you cannot create a constraint together with the database, the easiest way is to add it before entering data

ALTER TABLE Example 4

Modifying the structure of the table

ALTER TABLE t1 MODIFY (pole1 NOT NULL);

CREATE TABLE t2

(pole1 CHAR(10) PRIMARY KEY);

ALTER TABLE t1 ADD

(CONSTRAINT fk_t1 FOREIGN KEY (pole1)

REFERENCES t2 (pole1));

ALTER TABLE t1 ADD (UNIQUE (p_name));

ALTER TABLE t1 ADD (p_size CHAR(4) CHECK

(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));

The first of the above commands uses the MODIFY construct to add a NOT NULL constraint to a column, and uses the ADD construct to add all other types of tabular integrity constraints. The column for which you are adding a constraint must already exist in the database table; otherwise, the restriction cannot be created.

ALTER TABLE Example 5

To add integrity constraints, you can omit the name of the constraint to create by using the CONSTRAINT keyword. In this case, the command would look like this:

ALTER TABLE t1 ADD  FOREIGN KEY (pole1) REFERENCES t2 (pole1);

There are a number of conditions for creating restrictions:

  • Primary keys: Columns cannot contain null values, and all values must be unique.
  • Foreign keys: Those columns in other tables that are referenced must contain values that match all the values of the referencing columns, or the values of the reference columns must be null values.
  • UNIQUE constraints: All column values must be unique or null values.
  • CHECK constraints: The new constraint will only apply to data that is added or modified after it is created.
  • NOT NULL: Null values in columns are not allowed.

Restrictions can be allowed or denied. The allowed constraint performs its functions by implementing business rules against the data entered into the table, and the prohibited constraint is rendered invalid as if it had been deleted and its rules were not implemented.

ALTER TABLE Example 6

Prohibition of restrictions:

ALTER TABLE t1 DISABLE PRIMARY KEY;
ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Example 7

In some cases, disabling the primary key on which foreign keys depend can cause some difficulties, for example:

ALTER TABLE t2 DISABLEPRIMARY KEY;

Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)

To remove a primary key if there are foreign keys that depend on it, the ALTER TABLE DISABLE command <limits> the CASCADE construct is required:

ALTER TABLE t2 DISABLEPRIMARY KEYCASCADE;

ALTER TABLE Example 8

The prohibited restriction is allowed as follows:

ALTER TABLE t1 ENABLE PRIMARY KEY;

ALTER TABLE t1 ENABLE UNIQUE (p_name);

You can only allow restrictions that were previously set and are currently disabled.

A constraint whose creation failed will not exist in a forbidden form, waiting to be resolved after the error is resolved. Typically, the owner of the table or someone who has been granted the appropriate rights can remove the restriction:

ALTER TABLE t1 DROP UNIQUE (p_name);

ALTER TABLE — ADD column

To add a column to a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following SQL adds an "Email" blade for the "Customers" table:

Example

ALTER TABLE Customers
ADD Email varchar(255); Попробуйте сами »

ALTER TABLE — DROP COLUMN

To delete a column in a table, use the following command syntax (note that some database systems do not allow you to delete the column):

ALTER TABLE table_name
DROP COLUMN column_name;

The following SQL removes the "Email" column from the "Customers" table: