UNIQUE，UNIQUE KEY和CONSTRAINT'name'UNIQUE有什么区别? [英] What is the difference between UNIQUE, UNIQUE KEY and CONSTRAINT 'name' UNIQUE?

问题描述

我有一个要在MySQL中创建的基本users表.

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.

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;

推荐答案

1和2相同-都创建两个唯一索引，每个键一个. #3只能在两个键之间创建一个唯一索引，因此不能复制用户名和电子邮件的组合，但是例如，只要使用其他电子邮件，就可以复制用户名.

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.

听起来像您想要的前两个声音之一. UNIQUE和UNIQUE KEY是等效的.

Sounds like you probably want either of the first two. UNIQUE and UNIQUE KEY are equivalent.

这篇关于UNIQUE，UNIQUE KEY和CONSTRAINT'name'UNIQUE有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！