0

I have a table that has numerous records entered into it by hand. Through navicat that need to be able to be sorted by the order in which they were added. As of right now, I have an incrementing identity column called ID that is a primary key. But since it is the primary key, the column can not be copied and I get the error "field 'ID' can not be modified" every time I try to enter a new record. Can I have another data type that increments and can be duplicated? It doesn't matter if a few of them have the same ID value. I just need to have the records in general order of when they were added.

To add the id column originally I did this:

alter table accnt add ID int identity(1,1) not null

but I wanted for it not to be a primary key so I tried this with no luck:

alter table accnt add ID int numeric(1,1)

and

alter table accnt add ID int varchar(1,1)

all with no luck. how can I accomplish what I am trying to do?

viggity
  • 15,039
  • 7
  • 88
  • 96
Databasenoob
  • 31
  • 7
  • 4
    Why don't you have a datetime column and insert the value now() when you add a record. That way you will always know the order in which they were added. As a general rule, you shouldn't use a column (or any data) for two purposes - your ID column seems to both a unique identity column (always useful) and an 'insert-order' column – user2867342 Jul 30 '15 at 14:18
  • 4
    If you want an identity column that isn't a primary key, you can make one. Identity columns don't automatically become primary keys unless you explicitly make it one. – Tab Alleman Jul 30 '15 at 14:22
  • Not having a primary key is an awful idea. Don't this to yourself. Why can't you just create a new row and not provide the value for ID and let identity does its thing? And if you want rows in a certain order in your application you need to use an ORDER BY in your query. Anything else is not going to work correctly. – Sean Lange Jul 30 '15 at 14:23
  • I didn't explicitly make the identity column a primary key, but it still shows up as one when I create it – Databasenoob Jul 30 '15 at 14:31

2 Answers2

2

If you want to make sure that you can sort them in the order that they were added, just add a datetime field and make the default value be GETDATE()

ALTER TABLE acct
ADD CreateDate datetime DEFAULT GETDATE()

Now you can just order on the CreateDate

see this post for more info SQL Server default date time stamp?.

EDIT: based on comments below, here is how you could do this via a trigger (more info: https://msdn.microsoft.com/en-us/library/ms189799.aspx)

CREATE TRIGGER [dbo].[AcctInsert]
ON [dbo].[Acct]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE Acct 
    SET CreateDate = GETDATE()
    WHERE acctId in (
        select 
            acctId
        FROM Inserted
    )

END
Community
  • 1
  • 1
viggity
  • 15,039
  • 7
  • 88
  • 96
  • I have tried this before and it just gives me a bunch of null values – Databasenoob Jul 30 '15 at 14:45
  • 1
    my guess is that your ORM is automatically sending a null for the CreateDate field. If thats the case, I'd use a database trigger to overwrite the CreateDate on insert – viggity Jul 30 '15 at 14:56
1

Rather than inserting them in a particular order you should be thinking about using them in a particular order using a select statement. The order that the rows are held in a table when you add them are of little consequence to what you want to do. Your identity ID column can tell you what order they were added eg:

Select ID
FROM accnt
ORDER BY ID ASC

Select ID
FROM accnt
ORDER BY ID DESC
sarin
  • 5,227
  • 3
  • 34
  • 63
  • this is not what im saying. the records are added in order by which the person gets them then the sums are checked, so they have to be able to go back to change the records they just added – Databasenoob Jul 30 '15 at 14:38
  • When you display the data using navicat, which is a tool i am unfamiliar with you, should be able to sort the results by ID ? – sarin Jul 30 '15 at 14:40
  • you can. the issue is that when I have the primary key ID, sql server 2012 with not let me add more records or copy a record – Databasenoob Jul 30 '15 at 14:46
  • if you are adding a record into the accnt table you do not need to include the ID column value if it set as an Identity(1,1). An identity column will automatically increment the value without you having to worry about it – sarin Jul 30 '15 at 14:50
  • I know, I want the system to automatically replace what ever is in that column with the next number because records are copied all the time and the system will not let me copy a primary key because then it has two of the same – Databasenoob Jul 30 '15 at 14:53
  • Please clarify "I want the system to automatically replace what ever is in that column with the next number ". Your user is entering data into the sql table and you want the ID column to automatically increment + 1 from the last record? the interface you are using to add the record by hand should handle this for you if you do not supply an ID value – sarin Jul 30 '15 at 14:58
  • it does cover this if I don't put a value in that column. but a lot of times the record is copied from the previous record so the id column is the same. so the woman who is entering this data refuses to take the extra step of deleting the existing id value or replacing it with the new id. she insists that it does it by itself – Databasenoob Jul 30 '15 at 15:03
  • ah i'm beginning to understand the issue. The real issue is that you are giving an end user who doesn't understand how databases work a database management tool. You have two options. Either they start learning how to use a database and the tools provided or, you provide them with a user interface that doesn't require knowledge of how a database works. Probably not the answer you want, but you can't break the laws of physics (or relational databases) as a famous engineer once said.. – sarin Jul 30 '15 at 15:10
  • yeah pretty much. she enters around a thousand records in a sitting so adding 1 step to each record she adds will increase the time it takes to do it all exponentially – Databasenoob Jul 30 '15 at 15:57
  • You could copy them into excel first, remove the ID column for all of thenin one go and then paste them back in ? – sarin Jul 30 '15 at 16:03
  • Just had a thought. You could create a view on the table which selects out all columns apart from the ID column. The view will still be updateable which will allow her to paste the rows in? Ie she uses the view and not the table. Try that. – sarin Jul 30 '15 at 16:08
  • nope. it has to be done in sql. if I could just figure out why adding getdate () as the default doesn't work, I would be so happy – Databasenoob Jul 30 '15 at 16:08
  • https://msdn.microsoft.com/en-us/library/ms187956.aspx check the examples at the bottom. Create the view. Get your data entry lady to pen this view instead of the table to enter the data. – sarin Jul 30 '15 at 16:48