Click on “SQL database development”, \

Set it as “top or star mark” to deliver dry goods in the first time

ALTER TABLE ALTER TABLE

The ALTER TABLE command is used to add, delete, or change columns in an existing data TABLE.

You can also use the ALTER TABLE command to add or remove constraints on existing tables.

* * * *

** Sample database table **

Use the “Customers” table as an example

Add column syntax

The basic syntax for adding a new column to an existing TABLE with ALTER TABLE is as follows:

ALTER TABLE table_name 

ADD  COLUMN column_name datatype;

Add a new “ages” to the Customers table like this:

ALTER TABLE Customers ADD COLUMN age INT;Copy the code

Let’s look at the Customers table and add a column “age” at the end.

Since we don’t have a set value, the age column defaults to NULL.

Delete column syntax

The basic syntax for using ALTER TABLE to drop a column from an existing TABLE is as follows:

ALTER TABLE table_name 

DROP COLUMN column_name;

Delete the newly added “age” column from “Customers” above

ALTER TABLE Customers DROP COLUMN age;Copy the code

The “age” column is deleted after execution.

Modify column type syntax

The basic syntax for changing the data type of an existing TABLE column with ALTER TABLE is as follows:

ALTER TABLE table_name 

MODIFY column_name datatype;

We changed the “postcode” of the “Customers” table from CHAR to INT

ALTER TABLE customers MODIFY postcode INT;Copy the code

Add NOT NULL constraint syntax

The basic syntax for adding a NOT NULL constraint to a column with ALTER TABLE is as follows:

ALTER TABLE table_name 

MODIFY column_name datatype NOT NULL;

We can change the “provinces” in the “Customers” table from a NULL constraint to a NOT NULL constraint

ALTER TABLE Customers MODIFY VARCHAR(20) NOT NULL;
Copy the code

The results are as follows:

Add unique constraint syntax ****

The basic syntax for adding unique constraints to a TABLE with ALTER TABLE is as follows:

ALTER TABLE table_name 

ADD CONSTRAINT MyUniqueConstraint 

UNIQUE(column1, column2…) ;

We can add a unique constraint to the “names” in the “Customers” table

ALTER TABLE Customers ADD CONSTRAINT idx_ name UNIQUE;Copy the code

The results are as follows:

Add CHECK constraint syntax

ALTER TABLE ALTER TABLE add CHECK to TABLE

ALTER TABLE table_name 

ADD CONSTRAINT MyUniqueConstraint 

CHECK (CONDITION);

We can add a CHECK constraint to the “postcodes” in the “Customers” table, which must be 6 in length

ALTER TABLE Customers ADD CONSTRAINT con_ customer ID CHECK (length(postcode)=6);
Copy the code

When we insert a piece of data, the length of the zip code is not equal to 6, we will prompt an error, as shown below:

INSERT INTO customers 
VALUES('Joe'.'101 Zhongshan Avenue'.'guangzhou'.'510'.'Guangdong');
Copy the code

The results are as follows: \

When we change the length of the zip code to a six-digit length, we can insert normally.

INSERT INTO customers 
VALUES('Joe'.'101 Zhongshan Avenue'.'guangzhou'.'510000'.'Guangdong');
Copy the code

The results are as follows: \

Add primary key constraint syntax

ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE

ALTER TABLE table_name 

ADD CONSTRAINT MyPrimaryKey 

PRIMARY KEY (column1, column2…) ;

We first remove the self-growing primary key for the customer ID in Navicat, and then add the primary key constraint for the customer ID that does not have the self-growing attribute

ALTER TABLE Customers ADD CONSTRAINT pri_ PRIMARY KEY(customer ID);Copy the code

The results are as follows:

Delete constraint syntax

The basic syntax for dropping a constraint from a TABLE with ALTER TABLE is as follows:

ALTER TABLE table_name 

DROP CONSTRAINT MyUniqueConstraint;

We delete the CHECK constraint \ we just created

ALTER TABLE Customers DROP CONSTRAINT con_ customer ID;Copy the code

We can see that the direct constraint has been removed.

Other types of constraints can also be deleted using this syntax.

Note: Use ALTER TABLE with extreme care and make a full backup (backup of schema and data) before making changes. Changes to database tables cannot be undone, and if you add unwanted columns, you may not be able to delete them. Similarly, if you delete a column that should not be deleted, you may lose all data in that column

-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Can you guess which province has the most female breasts? ???? Carefully organized a set of SQL advanced functions, it is recommended to collect a SQL automatic check artifact, no longer need to worry about SQL errors! SQL statement after the where condition1=1Domestic database modeling tool, see the interface at first glance, conscience!Copy the code