One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key.
Oh, and the fact that a table can have one PK and many UK :-).
These are both differences in INTENT not in PERFORMANCE.
Otherwise, I don't think there's any difference.
Behind any PK or UK the SQL Server builds an index (depending on the request, clustered or not) and the way it's used is transparent for the source is coming from.
NuoDB is a completely new database system written from the ground up to exploit distributed shared-nothing architectures. NuoDB's "Emergent" architecture is the opposite of the monolithic approaches to SQL/ACID relational database systems that have dominated the last 3 decades. In NuoDB it is the aggregate effect of many nodes that electively opt-in and contribute to the system that results in it being a SQL/ACID database. It behaves like a flock of birds that fly in an organized fashion but without a central point of control or a single point of failure.
The product is not offered as a MySQL storage engine, partly because the MySQL storage engine API is a table-level API and does not allow the flexibility that a database system needs to do what NuoDB does. You can see a good video overview of the product at http://www.nuodb.com/blog.
On availability and pricing:
The product is designed specifically for web scale applications, supports PHP, Ruby, Hibernate and the usual range of SQL tools/APIs, and runs either on your own hardware or on public clouds.
NuoDB is in late Beta (Beta 6 is due in the next few weeks) with about 250 customers. The product is due to ship in the Spring.
Pricing depends on whether you download it or run it on public clouds. For download it will be free forever for small installations, and will have a subscription fee as you scale out. On public clouds it will be based on the pay-per-use model of the cloud.
Disclaimer: I am the CEO of NuoDB Inc
Best Answer
A table can have at most one
PRIMARY KEYconstraint but it can have as many as you wantUNIQUE KEYconstraints.Columns that are part of the
PRIMARY KEYmust be defined asNOT NULL. That is not required for columns that are part ofUNIQUE KEYconstraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.Another minor difference is that you can choose the name you want for a
UNIQUE KEYconstraint (and index). On the other hand, thePRIMARY KEYhas the default name:PRIMARY.