How to define a deferred constraint in MySQL

Question

is it possible to create a primary key in a existing table on a column that have repeated value? I want is previous record not validate but new record will validate with this.Is it possible in mysql. I know it is possible in Oracle (here is an example) but don't have idea about mysql.

Answer 1

No it can not be. It violates what Primary Key means. But if you want to have a composite primary key, it may be possible

Answer 2

A primary key is always a unique identifier, if you make it non unique it stops being an identifier, why do you want to repeat it? If you have multiple entries that have a field that repeats, that field is not your primary key, however, you can combine it with another field that will give you a primary key (not very recommendable, but you can make this field plus a timestamp field your combined primary key).

In this case what I would recommend is make an autoincrement key and just use this field that repeats as a normal field, maybe ad an index to it to improve searches. You can still look for records on any field, just because it's not your primary key it doesn't mean you are not going to be able to search and get it. The idea of a primary key is that it will get you 1 and only 1 record, not 1 or more.

Answer 3

The link you posted as a comment to Nerd-Herd's answer uses deferred constraints. Those constraints are checked at the end of the transaction rather than at the time the statement is executed.

MySQL does not support deferred constraints

If you absolutely need deferred constraints and want to stick with an open source database you will need to migrate to PostgreSQL.