sql - What does PRIMARY KEY actually signify, and does my table need one? -

I have a PostgreSQL 9.3 database with a user table that stores the user names in their case-protected format . All questions will be insensitive, so I should have an indicator that supports it. In addition, the user should be unique, regardless of the case.

This is what came with me:

  forums = & gt; \ D User table "public.users" column. Type | Modifier ------------ + -------------------------- + --------- --------------- Name | Character is changing (24). Null indexed: "users_lower_idx" UNIQUE, btree (short (name: text))  

expressed in standard SQL syntax:

  create table users (Name varchar (24) not tap); Create unique index "users_lower_idx" on users (lower (name));  

With this schema, I have satisfied all my hurdles, though without the primary key. The SQL standard does not support the functional primary key, so I can not promote the index:

  forums = & gt; Add user users_lower_idx to the optional table users using the primary key; Error: The index "users_lower_idx" expressions are LINE 1: Optional table users using the primary key User user_over_id ... ^ Description: Can not create primary key or unique barrier using such index.  

But, I already have a unique obstacle, and the column is already marked "NOT NULL". If I have a primary key, then I can create a table like this:

  create table user (name varchar (24) primary key); Create unique index "users_lower_idx" on users (lower (name));  

But then I will have two indexes, and if it seems useless and unnecessary to me, then the key key is "unique," beyond which there is a special meaning for postgres and Am I missing anything due to not being one?

"post-text" itemprop = "

provides additional modules, the data type of the same name" CI "insensitive copy documentation for the case:

The citext module provides a case-insensitive character string type, citext . Essentially, when comparing these values ​​internally Less calls otherwise, it behaves exactly like text .

This is exactly what is right for you. It is advisable:

Citex data type allows you to eliminate calls to reduce SQL queries, and a case insensitive to the primary key . .

bold thrust mine.
Be sure to read first. Install it according to the database once

  Create Extension Citext;  

text

If you do not want to go that route, Programming I would suggest that you surrogate primary key .

  create tab user (user_id serial primary key, do not tap username text);  

I will use text instead of varchar (24) . If you need to apply the maximum length (which can be changed later) then use the check constraint details:

• < / Li>

with the UNIQUE index in its original design):

  create unique index usersus_username_lower_idx on user (less (user name)) ;   
 The underlying  integer  of the  serial  is short and fast and do not waste time with  less  Or match your database This is particularly useful for foreign key references 
 
 Both solutions are professional and cons.