1

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

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.

1

There are many different ways of defining a primary key and I think its there to match with standard SQL. Below are 3 different ways of creating a primary key:

create table dept
(deptno decimal(2,0) PRIMARY KEY,
dname  varchar(14),
loc varchar(13));


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


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

and pk_dept is the index name. You can use it to drop or modify the index. as Micheal Green mentioned it is there to make your life easier. You can give it a name or let the system name it for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.