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 be descending.
views:
40answers:
3
+4
A:
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.
OMG Ponies
2010-07-08 22:09:46
There isn't an underlying index that is created when the FK is crated? My logic was if an index was created then I could control the sort order of the index.
Josh
2010-07-08 22:18:39
@Josh: No - IME, only primary keys typically get indexes automatically created. But indexing, which also doesn't care about order, on a foreign key is a very good idea.
OMG Ponies
2010-07-08 22:29:23
Sorry... no indexes are made when you make a FK. You should run this:create index IX_ActionLog on ActionLog (OrderID desc)
mattmc3
2010-07-08 22:29:45
Thanks -- I must be confusing a unique constraint with a FK contraint
Josh
2010-07-08 22:39:45
+2
A:
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.
JonPayne
2010-07-08 22:28:04
My motivation is that table will have millions of records, and when the queried by OrderID, only the more recent orders will be pulled. So having an index in descending would make sense to increase performance.
Josh
2010-07-08 22:42:16
The clustering index will do that for you, but it may not be the best choice of column to cluster on. It is dificult to say without knowing your schema. However, adding an ordinary index to the table on the OrderID column will also do what you want, so maybe adding an ordinary index is the way to go (as long as the table is already indexed by a different primary key or has a clustering index on another column).
JonPayne
2010-07-09 07:19:08
A:
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.
Cade Roux
2010-07-09 04:16:56