Mysql – Basic SQL question about setting constraints

constraintMySQLprimary-key

In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works? Is pk_dept something the database uses internally, since it doesn't seem to be a visible part of the table?

create table dept
    (deptno decimal(2,0),
    dname  varchar(14),
    loc varchar(13),
    constraint pk_dept primary key (deptno));

Best Answer

A primary key is an object that lives in the meta data of the database. It will have a name no matter how it is created. The question is will it have a meaningful name that matches your coding standards and that your team can understand at a glance or will it have a meaningless name the the system generates and everyone will continually have to look up?

The RDBMS does not care what string of characters constitute the object's name. It's a computer; all strings are equally meaningful/less.

To exaggerate the point to an extreme, you could have all your tables called T1, T2, T3 etc and columns C1, C2, C3. The application would work, the DBMS wouldn't care, but boy it would be hard to maintain!

You can get problems with replication for unnamed objects. The source and target systems know the objects by different names internally and replication gets confused trying to map the source to target.

Related Question