Creating a Unique SQL Table Constraint with Primary Key

Question

We are trying to enforce a unique table constraint on certain datatables in SQL Server, which I have working but I am running into a few issues. I want it to be ordered by Primary Key, but if I include that in the Index Keys, it no longer enforces uniqueness because it obviously will always have a unique ID since its a primary key.

If I remove the ID from the indexed keys, it works as it is supposed to but it no longer sorts by Primary Key anymore, which is what I want. It sorts by another one of the columns.

How do I include the primary key in the constraint so I can use it for sorting, but have it be ignored when checking the table constraint for uniqueness(ie, it should still not allow a new record to be written if all other info is the same other than ID)?

UPDATE: How do I handle a situation where a table has more columns than can be put into an index? Can I not enforce no duplicate entries in these?

Constraints don't order anything. No order is guaranteed at all if you don't include an ORDER BY clause in your query. Only by accident, you may get rows orderd by the clustered index order (which isn't necessarily the PK) if the query processor doesn't alter that order. Parallel processing is almost guaranteed to change the order of the results — Panagiotis Kanavos
OK Gotcha...I suppose it really doesn't make a difference as we can just put an ORDER BY Id in there on the Query and if we really needed to do the same thing on the return — MattE
for the updated part:you can try(stackoverflow.com/questions/15800250/…) — TheGameiswar

Shakeer Mirza Shakeer Mirza · Accepted Answer · 2016-12-23T16:30:41

A Relational database is built based on Set theory and Predicate logic. And according to Set theory There is no difference between sets like A {1,2,3} & B {2,3,1}. So this is the reason there is no guarantee in any RDBMS where results will come in particular order.

But you will get them in your order when you provide an ORDER BY in the SELECT statement explicitely.

So better you do it in front end or by adding an Order By clause to your query.

Creating a Unique SQL Table Constraint with Primary Key

1 Answers