CODEWITHSUNDEEP
mysqlsqldatabase-design
Sahil
Sahil

Reputation: 739

What would be proper schema for the following situation?

I need to create a database table in which I have the following three fields mainly:

  1. ID(default auto incrementing).
  2. User_id( starting from 1000 ,auto incrementing)
  3. Email

So, I want to set Email to be unique ,not null( Primary key ),but also I want to make sure that User_id remains unique ( that can be solved by setting unique key ) and I also want it to never be set as null. So, how I can achieve this? I am using MySQL BTW.

PS: I am not much good to this schema building for the current time. So, ignore any silly mistakes.

Upvotes: 0

Views: 59

Answers (2)

Fred
Fred

Reputation: 5808

I would go for something like this

CREATE TABLE USERS
(
        ID INT PRIMARY KEY AUTO_INCREMENT,
        User_Id INT AUTO_INCREMENT = 1000,
        Email VARCHAR(250) NOT NULL
)

And then 

ALTER TABLE USERS ADD UNIQUE (Email)

Or if you intend to search on the Email field

CREATE UNIQUE INDEX inx_users_email ON USERS  (Email)

You are better of using the Id field as your primary key. The PK is not meant to change and is used to identify the record. What happens if your user changes email address?

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Auto_INCREMENT ensures column has unique values and can never be null.

Upvotes: 0

Related Questions