How to add a new column with constraints in SQL

To add a new column with constraints to a table in SQL, use the ALTER TABLE statement, the syntax is: ALTER TABLE table_name ADD COLUMN column_name data_type [CONSTRAINT constraint_name constraint_type]. Adding constraints ensures the integrity and accuracy of the data. The steps include: determining the column name and data type, specifying constraints (optional, such as NOT NULL, UNIQUE, PRIMARY KEY), and finally running the ALTER TABLE statement.

Adding a new column with constraints to a table in SQL

In SQL, you can add new columns to an existing table by using the ALTER TABLE statement. Adding constraints ensures the integrity and accuracy of the data.

grammar:

 <code>ALTER TABLE table_name ADD COLUMN column_name data_type [CONSTRAINT constraint_name constraint_type];</code>

step:

1. Determine column name and data type: Select the name and data type to add a new column. The data type must be compatible with the data that is expected to be stored.

2. Specify constraints (optional): You can add constraints to limit the data type that is inserted into the column. Common constraint types include:

   • NOT NULL: This column is not allowed to have null values.
   • UNIQUE: Make sure that the values ​​in this column are unique.
   • PRIMARY KEY: Identifies the unique row of the table.
3. Run the ALTER TABLE statement: Use the above syntax to write the ALTER TABLE statement and execute it to the database.

Example:

Suppose you have a table named "Customers" and you want to add a new column named "phone_number" and limit it to a unique and non-null value.

 <code class="sql">ALTER TABLE Customers ADD COLUMN phone_number VARCHAR(15) NOT NULL UNIQUE;</code>

After executing this statement, the "phone_number" column is added to the "Customers" table with the following constraints:

• Data type: VARCHAR(15)
• Constraint name: None (default)

• Constraint type:

  • NOT NULL: No null values ​​are allowed
  • UNIQUE: The value must be unique

The above is the detailed content of How to add a new column with constraints in SQL. For more information, please follow other related articles on the PHP Chinese website!