gogoWebsite

Uniqueness constraints [MySQL][Database]

Updated to 2 days ago

Unique constraints (UNIQUE)

The role of uniqueness constraints:

Used to limit the value of a certain field to not be repeated (but there can be multiple nulls)

Characteristics of unique constraints:

  1. The same table can have multiple unique constraints
  2. The uniqueness constraint can be unique in the value of a certain column, or it can be unique in the value of a combination of multiple columns.
    • That is to say: our uniqueness constraint can be declared as a table-level constraint (it can be declared as a table-level constraint, and it can be declared as a composite constraint)
  3. Uniqueness constraint allows column values ​​to be empty
    • Allows multiple values ​​of fields with unique constraints to be null
  4. When creating a unique constraint, if the unique constraint is not named, the unique countername is the same as the column name by default.
  5. MySQL will create a unique index by default on the column with unique constraints, and the created unique index name is the same as the corresponding unique constraint name.

How to add uniqueness constraints?

Here we add uniqueness constraints in two ways

Method 1: Add unique index when CREATE TABLE

Let's give an example to illustrate how to add uniqueness constraints when CREATE TABLE

CREATE TABLE test2(
 # Here is a column-level constraint, that is, our unique constraint
 id INT UNIQUE,
 last_name VARCHAR(15),
 email VARCHAR(25),
 Salary DECIMAL(10,2)
 );
  • Note: When we create a table, if it is declared as a column-level constraint, then we cannot use the CONSTRAINT keyword to name the constraint.
  • Here is to add a unique constraint to the id field in the test2 table (declared here for column-level constraints)
CREATE TABLE test3(
 id INT UNIQUE,
 last_name VARCHAR(15),
 email VARCHAR(25),
 salary DECIMAL(10,2),
 #Here is a table-level constraint declared
 [CONSTRAINT uk_test2_email] UNIQUE(email)
 );
  • The content in [] above can be omitted. The content in [] is actually to give a name to the uniqueness constraint.
  • The CONSTRAINT uk_test2_email here actually gives this unique constraint a name: uk_test2_email
  • If we do not name the unique constraint when adding the unique constraint, the default is the same as the field name (column name)
After we have created the table and added a uniqueness constraint, we can find that in the data in the table, the values ​​of these fields with uniqueness constraints cannot be repeated, but multiple values ​​can be NULL at the same time, which means that multiple nulls are not the same.
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'tom','tom1@',4800);
  • This is the first time I added data after creating the table, there is no problem with this execution
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'tom','tom2@'4600);
  • At this time, the addition will fail because we add a unique constraint to the id field in the test2 table. At this time, the value of the id field in the data in the test2 table cannot be repeated. At this time, when we add this record, we determine that there is a record with id of 1 in our table. At this time, the id of the record we want to add cannot be 1, so an error will be executed at this time.
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'tom1',NULL,4600),
(s,'tom2',NULL,4600);
  • Adding at this time is successful, which means that when we add a unique constraint (UNIQUE) field, we can add null value, and we can add null value multiple times. Here we add two records with null value to the field email declared as unique in the table at one time. There is no error in execution at this time.
For unique constraints, we can not only declare as column-level constraints, but also as table-level constraints and table-level constraints, which means that we can declare as compound constraints (compound constraints: that is, constrain multiple fields)
So how do you declare a uniqueness constraint as a compound?
CREATE TABLE USER(
 id INT
 'name' VARCHAR(15),
 'password' VARCHAR(25),

 #Table-level constraints (at this time we declare this table-level constraint as a composite constraint)
 CONSTRAINT uk_user_name_pwd UNIQUE('name','password'),
 );
  • Here we declare a table-level constraint, and we declare that in order to comply with the constraint, why is it called a composite constraint?
    • At this time, we should constrain two fields (name and password) at one time with the same constraint.
Note: When declared as a conforming uniqueness constraint, it is only if the values ​​of these fields of the composite uniqueness constraint are the same.

eg:

INSERT INTO USER
VALUES(1,'tom','abc'),
(1,'tom1','abc');
  • We know that before we added the unique constraints of the composite to the name and password fields. At this time, when the values ​​of the name and password fields are the same, it means that there is no duplication. Here, only the password value is repeated, and the value of name is not repeated, and the addition will be successful at this time.
Method 2: Add uniqueness constraints when ALTER TABLE

In case 2, we have two ways to add unique constraints:

Method 1: ADD
ALTER TABLE test2
ADD [CONSTRAINT uk_test2_sal] UNIQUE(salary);
  • When adding uniqueness constraints in this add method, the format of adding table-level constraints is similar to the previous one when adding CREATE TABLE
Method 2: MODIFY
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
  • When adding unique constraints in this MODIFY method, the format of adding column-level constraints was similar to the previous one when we added CREATE TABLE.

So how to delete uniqueness constraints?

We need to know before deleting the uniqueness constraint:

  1. Fields that add uniqueness constraints will also automatically create uniqueness indexes.
  2. Deleting uniqueness constraints can only be deleted by deleting unique indexes.
  3. When deleting a unique index, you must specify a unique index name. The unique index name is the same as a unique constraint name.
  4. If the constraint name is not specified when creating a unique constraint, then if it is a single column constraint, the default is the same as the column name. If it is still a composite constraint, the default is the same as the column name of the first column in the combined column.
Then here we will give an example of how to delete a unique index (DROP INDEX)

Note: The corresponding uniqueness constraints will be automatically deleted after the uniqueness index is deleted.

ALTER TABLE test2
DROP INDEX last_name;
  • When we add uniqueness constraints to the last_name field in the test2 table, we add it in the form of column-level constraints when CREATE TABLE. When we add uniqueness index, we cannot specify the constraint name. Then the unique constraint name is the same as our last_name column name by default. Then the unique index name is the same as the unique constraint name, so it is all last_name
ALTER TABLE test2
DROP TABLE uk_test2_sal;
  • Here, when we add a unique constraint to the salary field in the test2 table, it is added in the form of a table-level constraint when CREATE TABLE. When we add this unique constraint, we specify the name of the unique constraint: uk_test2_sal, then our unique index name is: uk_test2_sal