JDay2
asked on
AutoIncrement column based of FK
I have a table
OrderDetail
PK FK
OrderDetailID OrderID LineNo
8238 7780 1
8239 7780 2
8240 7781 1
How do I create the column LineNo based of OrderID to increment starting with one? So once the FK OrderID changes it starts back to 1 for the next orderid. The lineno field must autogenerate and needs to be stored.
OrderDetail
PK FK
OrderDetailID OrderID LineNo
8238 7780 1
8239 7780 2
8240 7781 1
How do I create the column LineNo based of OrderID to increment starting with one? So once the FK OrderID changes it starts back to 1 for the next orderid. The lineno field must autogenerate and needs to be stored.
why do you need to store it if you can easily generate it?
another issue you might have if you store that value is that if you remove rows from your table, you will get holes in your LineNo sequence
ASKER
I need to store it because it will be tied with order detail information and the line number makes it unique.
I don't want to use the PK field and the viewing of line no per order seems logical.
I will have it so you cannot remove rows and have an option to cancel as a status.
I don't want to use the PK field and the viewing of line no per order seems logical.
I will have it so you cannot remove rows and have an option to cancel as a status.
it would be easier if your client application handling the orders would provide that value.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
can I make this a stored procedure to calculate?
How do you add rows to your table?
ASKER
by sql business rule for that field in .net c#
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are not calling a Stored Proc to do the insert, you cannot have a SP to set the value! That's why the article of sqlteam is suggesting a trigger.
If your .Net code is inserting the data directly, better have the .Net code to set the LineNo value as well.
If your .Net code is inserting the data directly, better have the .Net code to set the LineNo value as well.
JDay2, a feedback will be appreciated.
Cheers
Cheers