Add Sort Order for FOREIGN KEY CONSTRAINT on SQL Server

How do I add the Foreign Key Sort order in the following statement:

ALTER TABLE [dbo].[ActionLog] 
 WITH CHECK ADD CONSTRAINT [FK_ActionLog_Order] FOREIGN KEY([OrderID])

I want the OrderID to b开发者_如何学JAVAe descending.

A foreign key constraint only ensures the values already exist in the table referenced, not order. For referential integrity, the database doesn't care what order of the data is.

The only way to ensure order in a resultset is to use an ORDER BY clause.

Having an order on a foreign key is nonsense. A foreign key is a way to impose a rule that the value in the OrderId field (in your example) must exist in another table. It has nothing to do with the clustering of your table (which is the only way to impose an order in a table).

Incidentally, you haven't shown the complete statement as there shuold be a REFERENCES table(column) at the end of your ADD CONSTRAINT statement.

If you really want the data in your table to be stored in OrderID order then you need to add a clustering index, such as

CREATE UNIQUE CLUSTERED INDEX CIX_Action_log
   ON Action_log (OrderID)
GO

But I have to question your motives for doing this.

A foreign key constraint on ActionLog.OrderID referencing Orders(OrderId) will require that the columns referenced in Orders do form a unique key, thus there will need to be some kind of constraint or index on the referenced table before you can apply the foreign key constraint in the first place. At that point, you could specify the index order on the referenced table. Such a constraint is for getting from ActionLog to Orders efficiently, and typically does not have any/much say in accessing ActionLog.

If you need to pull ActionLog by OrderID, you would have to add an index yourself.