unique really unique?

Anon

I have the following schema:

CREATE TABLE mycontacts (
m_key INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
m_contact_key INT NOT NULL,
m_empl_key INT NOT NULL,
m_cwho INT NOT NULL,
m_cwhen datetime NOT NULL
);

I add a record... delete it... and add another

Both have the same keys... is there a way to specify to never repeat a key? I was wondering if unique would do it... I've looked on mysql.com site for "unique" but their search wasn't working.

Thanks.

Anon

No.

Auto increment keys should only be used internally in the database to create relations between tuples in tables. I think the outside world should not be aware of these keys. This might be annoying if you depend on the unique key being unique over time.

Web sites that use keys to refer to an article/news item should use a composite key/index with auto increment combined with for example date/time or another solution. Personally I prefer to use an md5 hash that I first check the database for to make sure it is unique.

Anon

You could ofcourse use the LAST_INSERT_ID() when inserting new posts. I'm not sure how safe it'll be but maybe it's ok.

INSERT INTO myTable(myKey, field)
VALUES (LAST_INSERT_ID()+1, 'wow');

Try it out but as I said - I'm haven't tested it and I'm not sure it works.

Anon

well I want to do it when I delete a record from the database is to save to xml and then in the future restore that record to the database if it was deleted by accident.

Its not that big of a deal, I don't have to store the id number.

So, can you give me an example of how you use an md5 hash to generate a unique id?

Anon

Use postgres. It has full sequence support. With a sequence you can specify start value, max value, wraparound and they always monotonically increase.

Chris