Oracle - primary key(col) vs constraint tablex

Is there any key difference between two approaches to enforce the primary key constraint (at table level)?

CREATE TABLE employee(
   empNum  number(6),
   empName varchar2(50),
   PRIMARY KEY (empNum, empName) --intentionally set
);

CREATE TABLE employee(
   empNum  number(6),
   empName varchar2(50),
   constraint empNum_PK primary key (empNum, empName)
);

Just want to know the best practice from here as what displayed when search "ways to set primary key constraint", it tells nothing but only tutorials to set and do not mention the difference.

CodePudding user response：

No difference at all - except that in your 1st example Oracle has to name the constraint (and it'll be something like SYS_C008630 so you have no idea what it represents), while you named the constraint as empNum_PK which shows it is the primary key.

There's yet another option: alter table:

SQL> create table employee(
  2    empNum   number(6),
  3    empName  varchar2(50));

Table created.

SQL> alter table employee add constraint empNum_PK primary key (empNum, empName);

Table altered.

SQL>

Anyway, there's still no difference as it is the primary key.

There would be a difference if it were a foreign key in cases where there's a circular reference between two tables (where each of them references another). Without alter table option, you couldn't create foreign key constraint.

This, obviously, won't work (as referenced tables don't exist yet):

SQL> create table tab_2 (id_b   number primary key,
  2                      id_a   number constraint fk_21 references table_1 (id_a));
                    id_a   number constraint fk_21 references table_1 (id_a))
                                                              *
ERROR at line 2:
ORA-00942: table or view does not exist

But, if you first create tables and then foreign keys, then it works:

SQL> create table tab_1 (id_a    number  primary key,
  2                      id_b    number);

Table created.

SQL> create table tab_2 (id_b   number primary key,
  2                      id_a   number);

Table created.

SQL> alter table tab_1 add constraint fk_12 foreign key (id_b) references tab_2 (id_b);

Table altered.

SQL> alter table tab_2 add constraint fk_21 foreign key (id_a) references tab_1 (id_a);

Table altered.

SQL>

At the end, the final result is just the same: all those keys (primary, as in your example, or foreign, as in my example) are created and functional.

What is the best practice? If you're preparing an installation script you'll run in some schema and it'll create tables, views, constraints, ..., then consider my 2nd example: create table first, and create constraints separately. Other than that, I don't think that it matters whether you create constraints within the CREATE TABLE statement or using ALTER TABLE.