change my PK in table

If i have a table called university, with two fields, one for id_university, that is PK and other for name_of_university. Each name of university is unique and cannot be repeated.

In this case i can remove the id_university, and put the name_of_university as Pr开发者_如何学Pythonimary key, correct?

Something like this:

Table

university
-----------------------
pk name_of_university

thanks

---

You can do this but you shouldn't. The name of the university is a business key, and as such is liable to change. One of the criteria for identifying candidate primary keys is that they should be invariant.

So, best practice is to have a surrogate (synthetic) primary key, for use in foreign keys, etc and maintain a unique constraint on the business key. So, the good news is, your current data model is close to being best practice. Just add a unique on the name column and you're good to go.

 alter table university
      add constraint uni_name_uk unique (name_of_university);

---

It's good practice to leave the primary key as id_university and just add a unique index on name_of_university.

---

Yes correct.

But not recommended.
Long PK's slow everything down, way way down.

And on InnoDB the PK is included on every secondary key, this will balloon your table.
Join's will be slower, inserts will be slower, sorts will be slower.
And your tables will be bigger. It's a really bad idea :-Sorry

Keep your PK as short as possible and autoincrement (if possible), this will result in snappy, happy code.

---

Yes that's correct. A key is a key. If you have more than one key then it makes no real difference which key you call your primary one. What matters is the way you intend to implement and use them.