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,140 points
18 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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,570 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,260 points
32 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,100 points
19 views
0 votes
1 answer

What is checkpoint in Database?

Checkpoint declares a point before which all ...READ MORE

answered Sep 4, 2018 in Database by darklord
• 6,140 points
53 views
0 votes
1 answer

Difference between primary key a composite key

Primary key is that column of the table ...READ MORE

answered Sep 5, 2018 in Database by CodingByHeart77
• 3,680 points
736 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,570 points
50 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,520 points
30 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 parth295
• 4,590 points
87 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,570 points
25 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,570 points
52 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.