From my understanding, PUT can be used to insert or modify existing record.
PUT to a URL creates/replaces the resource in its entirety at the client defined URL.
PUT: www.domain.com/accounts/1
{
Name: "Some Name"
}
Following request would create account with ID 1, but in the next request it would simply update/replace account with ID 1 which makes it idempotent.
How does this work with identity columns in relational databases, or any other database which supports auto generated identity?
If my accounts have auto generated SQL identity column, should I use property other than primary key to identify that resource in the database, or it is perfectly fine to return resource not found error upon request for non existing resource?
First, I would use POST instead of PUT for creating a new resource.
Second, using the DB PK as the resource unique identifier is fine, although in some scenarios you might want to back that column with a guid one (say if you were implementing a voucher system and wouldn't want users to try and hack it by incrementing the voucher id - you'd expose just the voucher guid).
As for the actual id, I think you would be fine omitting it from the initial POST payload - it's just an implementation detail, the DB is the only one that should generate it, and it will get returned in the response of that (and further) request(s).