DateTime.Now as Primary Key

[Harris]

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

 

[edgar_ortiz]

Good topic for a discussion.

2. Avoid dups from AutoInc

???

In the past i design database really LARGE... never have a problem with Auto-Increment

Check:

sql - What's the best practice for primary keys in tables? - Stack Overflow

In my way of thinking... the best is a Auto-increment... try to INSERT if fail... try again.

Regards,

Edgar

P.D.:
Do you solve your previous problem (insert in a table?)

 

[corwin42]

You will not avoid dups with DateTime.Now. What if you insert two rows within the same millisecond?

sent from my Galaxy Nexus

 

[Harris]

Do you solve your previous problem (insert in a table?)

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

 

[Harris]

You will not avoid dups with DateTime.Now. What if you insert two rows within the same millisecond?

sent from my Galaxy Nexus

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

 

[Harris]

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

 

[Mahares]

Yes. SQLite supports one PRIMARY KEY, but the primary key can be multi-column. I use this type of example often:

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:

'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