CODEWITHSUNDEEP
mysqldatabasedatabase-design
user455318
user455318

Reputation: 3346

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 Primary key, correct?

Something like this:

Table 

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

thanks

Upvotes: 3

Views: 101

Answers (4)

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 0

APC
APC

Reputation: 146219

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);

Upvotes: 4

Johan
Johan

Reputation: 76557

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.

Upvotes: 0

Kelly
Kelly

Reputation: 41531

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

Upvotes: 2

Related Questions