0

This potentially might be too large of a question for a complete solution, and I've got a bit of a strange set up. I'm using HP OO to create a text-based RPG just to practice getting used to database design on this platform.

So it's basically a flow script that runs once. When the script starts, a player (user) is created, and then a character is created. The player inputs a name for its character, and this is stored in the character table. I then call that character name with SELECT name FROM character WHERE character.character_id=x. How can I retrieve the name from the correct (most recently created) character. The character_id is an auto-incrementing identity column.

Martin Erlic
  • 5,467
  • 22
  • 81
  • 153

3 Answers3

4

There's nothing guaranteeing that the highest value in an identity column is the most recently created record. You should add a date_created column to your table and give it a default value of the current date and time (current_timestamp for a datetime2 field). That actually does what you want.

OK, your question changed a bit and, Tab's comment here is also correct. If you want to insert and get the identity inserted back, you should follow the advice here that he linked.

However, if you want to be able to determine the order of creation -- which is what you originally asked -- then you should use a date_created field. It's possible to get around IDENTITY and insert any value you want, and things like UPDATEs and DELETEs can change things as well. Essentially, it's a bad idea to assign meaning to a record's value of an IDENTITY column relative to other records in the table (i.e., this was created before or after these other records) because you can actually get around that.

Personally, I would either use the OUTPUT clause to have my INSERTs send the ID back:

INSERT INTO Character (...)
OUTPUT INSERTED.Id
VALUES (....);

Or I'd reuse the same connection and return the SCOPE_IDENTITY().

INSERT INTO Character (...)
VALUES (....);

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
Community
  • 1
  • 1
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • 1
    Scope_Identity is actually safer if concurrency can be an issue. – Tab Alleman Apr 21 '16 at 14:50
  • Is the time stamp an identity column? Should I set it to be? – Martin Erlic Apr 21 '16 at 14:53
  • @bluemunch No, only integers can be IDENTITY columns. And *do not* use the creation timestamp as a unique identifier, and certainly not as a primary key. That's a nightmare. – Bacon Bits Apr 21 '16 at 15:02
  • @TabAlleman I agree. The question is clearer now than it was. – Bacon Bits Apr 21 '16 at 15:04
  • Okay, so then in the context of this question specifically (one-time executed flow script), the correct approach would be ``SELECT name FROM character WHERE character.character_id=IDENT_CURRENT('character')``? Since there is no concurrency, and only one user, choosing the most recently created character_id by identity is the way to do it? Seems to work! – Martin Erlic Apr 21 '16 at 15:06
1
SELECT row FROM table WHERE id=(
    SELECT max(id) FROM table
    )
this should work

Make sure the id is unique (auto increments? great!)

shb
  • 5,957
  • 2
  • 15
  • 32
0

When you insert into the character table, you should rely on Scope_Identity() or @@Identity (depending on how your database is setup. Read more about it here) to return the unique ID (the auto incremented ID) from the table, and use that moving forward.

Doing it any other way opens you up to data integrity issues.

Jake
  • 554
  • 3
  • 10