I have a basic users
table I want to create in MySQL.
I do not want duplicate emails or duplicate usernames appearing in the database.
1.
UNIQUE (username), UNIQUE (email),
2.
UNIQUE KEY (username), UNIQUE KEY (email),
3.
CONSTRAINT ucons_login UNIQUE (username, email),
I assume some of these are synonymous, yet I've been reading conflicting information online and was seeking confirmation.
I hope someone can assist.
The SQL:
CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
pass CHAR(40) NOT NULL,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
registration_date DATETIME NOT NULL,
user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
active CHAR(32),
PRIMARY KEY (user_id),
UNIQUE (username),
UNIQUE (email),
INDEX login (email, pass),
INDEX full_name (last_name, first_name)
) ENGINE=INNODB;
A unique constraint is the rule that the values of a key are valid only if they are unique. A key that is constrained to have unique values is called a unique key . A unique constraint is enforced by using a unique index.
A unique index ensures that the values in the index key columns are unique. A unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).
Unique Key: It is a constraint which imposes limitation on database. That limitation is it will not allow duplicate values . For example if you want to select one column as primary key it should be NOT NULL & UNIQUE. Unique Index: It is a index which improves the performance while executing queries on your data base.
A constraint has different meaning to an index. It gives the optimiser more information and allows you to have foreign keys on the column, whereas a unique index doesn't.
1 and 2 are identical - both create two unique indexes, one for each key. #3 only creates one unique index across both keys, so no combination of username and email can be duplicated, but for example, a username could be duplicated as long as a different email was used.
Sounds like you probably want either of the first two. UNIQUE and UNIQUE KEY are equivalent.
They are all synonymous as evidenced by syntax documentation:
[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option]
[]
in this notation (Wirth's notation) denote optional elements
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With