SQL ALTER TABLE statement to flexibly modify table structure and data types

SQL ALTER TABLE statement

SQL ALTER TABLE statements are used to add, delete, or modify columns in an existing table, and can also be used to add and remove various constraints.

ALTER TABLE - Adding Columns

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

ALTER TABLE table name
ADD listings data type;

The following SQL adds an "Email" column to the "Customers" table:

ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE - Delete Columns

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

ALTER TABLE table name
DROP COLUMN listings;

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

ALTER TABLE Customers
DROP COLUMN Email;

ALTER TABLE - Rename Columns

To rename columns in a table, use the following syntax:

ALTER TABLE table name
RENAME COLUMN former name TO new name;

ALTER TABLE - Modify Data Type

To change the data type of a column in a table, use the following syntax:

For SQL Server / MS Access:

ALTER TABLE table name
ALTER COLUMN listings data type;

For MySQL / Oracle (versions prior to 10G):

ALTER TABLE table name
MODIFY COLUMN listings data type;

For Oracle 10G and later:

ALTER TABLE table name
MODIFY listings data type;

SQL ALTER TABLE Example

Look at the "Persons" table:

ID  LastName  FirstName  Address  City
1  Hansen  Ola  Timoteivn 10  Sandnes
2  Svendson  Tove  Borgvn 23  Sandnes
3  Pettersen  Kari  Storgt 20  Stavanger

Now, we want to add a column named "DateOfBirth" to the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date;

Note that the new column "DateOfBirth" has a data type of date and will hold the date.

"Persons" is expressed as follows:

ID  LastName  FirstName  Address  City  DateOfBirth
1  Hansen  Ola  Timoteivn 10  Sandnes   
2  Svendson  Tove  Borgvn 23  Sandnes   
3  Pettersen  Kari  Storgt 20  Stavanger

Example of changing the data type

Now, we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

Note that the "DateOfBirth" column now has a data type of year and will hold the year in either two or four digit format.

Example of Deleting Columns

Next, we want to delete the column named "DateOfBirth" from the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

"Persons" is expressed as follows:

ID  LastName  FirstName  Address  City
1  Hansen  Ola  Timoteivn 10  Sandnes
2  Svendson  Tove  Borgvn 23  Sandnes
3  Pettersen  Kari  Storgt 20  Stavanger

Hopefully, these examples will help you better understand the usage of the SQL ALTER TABLE statement.

SQL Constraints

SQL constraints are used to specify rules for the data in a table to ensure the accuracy and reliability of the data. Constraints can be specified when a table is created, or after the table is created using theALTER TABLE statement is added.

Specifying constraints when creating a table

CREATE TABLE table name (
    columns1 data type austerity,
    columns2 data type austerity,
    columns3 data type austerity,
    ....
);

Commonly used constraint types

1. NOT NULL constraint

Ensure that columns cannot have NULL values.

CREATE TABLE table name (
    columns1 data type NOT NULL,
    columns2 data type,
    columns3 data type,
    ....
);

2. UNIQUE constraint

Ensure that all values in the column are distinct.

CREATE TABLE table name (
    columns1 data type UNIQUE,
    columns2 data type,
    columns3 data type,
    ....
);

3. PRIMARY KEY constraint

is a combination of NOT NULL and UNIQUE constraints that uniquely identifies each row in a table.

CREATE TABLE table name (
    columns1 data type PRIMARY KEY,
    columns2 data type,
    columns3 data type,
    ....
);

4. FOREIGN KEY constraint

Used to prevent operations that break relationships between tables.

CREATE TABLE table name1 (
    columns1 data type PRIMARY KEY,
    columns2 data type,
    columns3 data type,
    ....
);

CREATE TABLE table name2 (
    columnsA data type,
    columnsB data type,
    columnsC data type,
    FOREIGN KEY (columnsA) REFERENCES table name1(columns1)
);

5. CHECK Binding

Ensures that the values in the columns meet specific conditions.

CREATE TABLE table name (
    columns1 data type,
    columns2 data type CHECK (columns2 > 0),
    columns3 data type,
    ....
);

6. DEFAULT constraint

If no value is specified, a default value is set for the column.

CREATE TABLE table name (
    columns1 data type,
    columns2 data type DEFAULT default value,
    columns3 data type,
    ....
);

7. CREATE INDEX constraint

Used to quickly create and retrieve data from databases.

CREATE INDEX index name
ON table name (columns1, columns2, columns3, ...);

These constraint types can be used in flexible combinations based on table design and requirements to ensure data integrity and consistency in the database.

Above is the SQL ALTER TABLE statement - flexible modification of the table structure and data type details, more information about SQL ALTER TABLE modify the table structure data please pay attention to my other related articles!