DateTime.Now as Primary Key

Harris

Expert
Licensed User
Longtime User
Do you think it would be safe (wise) to use DateTime.Now as Primary Key (to provide unique). What are the chances of many users of your app creating a duplicate pkey at the same millisecond (if you sync to a server that would raise a key viol error)?

Benefits:

1. Easy to maintain - get the Now time.
2. Avoid dups from AutoInc or your own Pkey incrementer across multiple devices synced on a cloud server.
3. ( ? )

Pitfalls:

1. Long (4 bytes) - big deal?
2. ( i don't know)

Your comments most welcome.

Thanks
 
Sort by date Sort by votes
E

edgar_ortiz

Active Member
Licensed User
Longtime User
Last edited:
Upvote 0

Harris

Expert
Licensed User
Longtime User
Do you solve your previous problem (insert in a table?)

No, and it is making me crazy....
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
corwin42 said:
You will not avoid dups with DateTime.Now. What if you insert two rows within the same millisecond?

sent from my Galaxy Nexus
Click to expand...

Yes, never thought of that one...

My concern with auto or my own inc method (start at 1000 and inc from there) is this:

Three (or more) devices report their records to a MySQL hosted database.
Device one: Insert into Task table with PK of 1001.
Device two: Insert into Task table with PK of 1002.
Device three: Insert into Task table with PK of 1001. - KEY VIOL!

Each device starts out with the same empty dataset and works from there.

This is the reason I reasoned that Now may provide a more suitable substitute for Auto Inc.

Thanks
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
After reading up on Edgar's link, I could use a compound PK to avoid dups when inserting my hosted DB.

Something like PK and DT:

PK - Autoinc
DT - Datetime.now
Name...
Address...

Does SQLite support this creation?

Thanks
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Yes. SQLite supports one PRIMARY KEY, but the primary key can be multi-column. I use this type of example often:
B4X: 
txt = "CREATE TABLE MyTable (Field1 TEXT NOT NULL, Field2 TEXT NOT NULL, Field3  TEXT, PRIMARY KEY (Field1,Field2))"
SQL1.ExecNonQuery(txt)

@Harris: In your case you can have something like this:
B4X: 
'BELOW TO CREATE A TABLE WITH MULTI COLUMN PRIMARY KEY
       txt="DROP TABLE if exists MyTable"
       SQL1.ExecNonQuery(txt)
       txt = "CREATE TABLE MyTable (DT INTEGER NOT NULL, NAME TEXT NOT NULL, ADDRESS TEXT, PRIMARY KEY (DT, NAME))"
         SQL1.ExecNonQuery(txt)
       txt="INSERT INTO MyTable VALUES(?,?,?)"
       SQL1.ExecNonQuery2(txt,Array As Object(DateTime.Now,"Harris","UK"))
       SQL1.Close
 
Last edited:
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…