In a database, uniqueness constraints ensure that each row of data in a specific column of a table is distinct. This can be achieved using a unique index or a primary key. When these constraints are violated, the database operation will fail.
Example of Failure:
Consider a users table with two columns: user_id and email. user_id is the primary key, and the email column has a unique index. The table structure is as follows:
CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(255) UNIQUE );
The table already contains the following data:
| user_id | |
|---|---|
| 1 | alice@example.com |
| 2 | bob@example.com |
Now, if we try to insert a new user with an email address that already exists, it will cause a failure:
sql複製程式碼INSERT INTO users (user_id, email) VALUES (3, 'alice@example.com');
This INSERT statement will fail because the email column already contains an entry with the value "alice@example.com," which violates the uniqueness constraint.
Example of Success After Checking:
To avoid the failure described above, we can perform a check before inserting the data. Below are two methods for checking uniqueness:
Method 1: Query Check
Before inserting new data, you can first check if the email already exists:
SELECT * FROM users WHERE email = 'charlie@example.com';
If the query returns no results, it means the email is unique, and you can safely insert the data:
INSERT INTO users (user_id, email) VALUES (3, 'charlie@example.com');
Method 2: Conditional Insert
Another approach is to use a conditional insert statement provided by the database (such as MySQL's INSERT IGNORE or PostgreSQL's ON CONFLICT DO NOTHING):
In MySQL:
INSERT IGNORE INTO users (user_id, email) VALUES (3, 'charlie@example.com');
In PostgreSQL:
INSERT INTO users (user_id, email) VALUES (3, 'charlie@example.com') ON CONFLICT (email) DO NOTHING;
These statements attempt to insert the data, but if a uniqueness constraint conflict occurs, the operation is ignored, and no data is inserted. No error will be thrown.
These two methods can be chosen depending on the database management system being used. In this example, using "charlie@example.com" as the new email address is safe because it is unique in the database, so the insert will succeed.
