Which one is suitable for real time big database- Primary Key or Unique Index

0 votes
At work we have a big database with unique indexes instead of primary keys and all works fine.

I'm designing new database for a new project and I have a dilemma:

In DB theory, primary key is fundamental element, that's OK, but in REAL projects what are advantages and disadvantages of both?

What do you use in projects?
Oct 12, 2018 in Database by Neha
• 6,300 points
1,550 views

1 answer to this question.

0 votes

What is a unique index?

A unique index on a column is an index on that column that also enforces the constraint that you cannot have two equal values in that column in two different rows. Example:

CREATE TABLE table1 (foo int, bar int);
CREATE UNIQUE INDEX ux_table1_foo ON table1(foo);  -- Create unique index on foo.

INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK
INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails!

Duplicate entry '1' for key 'ux_table1_foo'

The last insert fails because it violates the unique index on column foo when it tries to insert the value 1 into this column for a second time.

In MySQL a unique constraint allows multiple NULLs.

It is possible to make a unique index on mutiple columns.

Primary key versus unique index

Things that are the same:

  • A primary key implies a unique index.

Things that are different:

  • A primary key also implies NOT NULL, but a unique index can be nullable.
  • There can be only one primary key, but there can be multiple unique indexes.
  • If there is no clustered index defined then the primary key will be the clustered index.
answered Oct 12, 2018 by Frankie
• 9,830 points

Related Questions In Database

0 votes
1 answer

Using Real Time flume Data for Analysis

By using MorphlineSolrSink we can extract, transform ...READ MORE

answered Jul 17, 2018 in Database by kurt_cobain
• 9,390 points
576 views
0 votes
1 answer

Which operator is used in the query for pattern matching?

LIKE operator is used for pattern matching, ...READ MORE

answered Oct 15, 2018 in Database by DataKing99
• 8,240 points
22,704 views
+1 vote
1 answer

Do I need to mention primary key for each table?

Hi Pritha, First, there is no hard and ...READ MORE

answered Jul 4, 2019 in Database by sampriti
• 1,120 points
662 views
0 votes
1 answer

Which is faster/best? SELECT * or SELECT col1, col2,......colN

There are four big reasons that select * is ...READ MORE

answered Sep 27, 2019 in Database by Omaiz
• 560 points
610 views
0 votes
1 answer

Can I have multiple primary keys in a single table?

A Table can have a Composite Primary Key which ...READ MORE

answered Oct 25, 2018 in Database by Frankie
• 9,830 points
3,319 views
0 votes
1 answer

Is it possible to access Couchbase by terminal?

What you want to accomplish isn't possible ...READ MORE

answered Apr 29, 2018 in DevOps & Agile by DareDev
• 6,890 points
473 views
0 votes
1 answer

How to connect Java program to the MySQL database?

You can connect your Java code with ...READ MORE

answered May 11, 2018 in Java by Parth
• 4,630 points
1,590 views
0 votes
1 answer
0 votes
1 answer

What is a covered Index?

A covering index is an index that contains all ...READ MORE

answered Oct 10, 2018 in Database by Frankie
• 9,830 points
587 views
0 votes
1 answer

How to connect to MySQL Database?

using MySql.Data; using MySql.Data.MySqlClient; namespace Data { ...READ MORE

answered Oct 12, 2018 in Database by Frankie
• 9,830 points
941 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP