Different styles for assigning a primary key in oracle sql-CodePudding

I was testing the following examples (1, 2 and 3) in APEX-Oracle SQL commands and all three examples seems to work fine, however, I thought the PRIMARY KEY is named/reserved keyword in oracle, so the question is there any difference between the primary keys in these three examples.

Please note that example_2 the primary key is in small letters

CREATE TABLE example_1(
    ID int  PRIMARY KEY,
    LastName VARCHAR2(255) NOT NULL,
    FirstName VARCHAR2(255),
    Age int
);

CREATE TABLE example_2(
    ID int  primary key,
    LastName VARCHAR2(255) NOT NULL,
    FirstName VARCHAR2(255),
    Age int
);

CREATE TABLE example_3(
    ID int ,
    LastName VARCHAR2(255) NOT NULL,
    FirstName VARCHAR2(255),
    Age int,
    CONSTRAINT PK_example_3 PRIMARY KEY (ID)
);

CodePudding user response：

No difference in functionality. Only difference is that for table example_3 the primary key is a named constraint and not a generated name.

"PRIMARY KEY" is not a reserved word (full list here) and case doesn't matter in the CREATE TABLE clause (object names can be case sensitive if enclosed in quotes)

You can easily check this yourself... query data dictionary USER_TABLES for details about the tables and USER_CONSTRAINTS to see if there are differences in the primary key constraints.

CodePudding user response：

In Oracle INT is synonym for NUMBER(38) and that's bigger than a quad-precision integer. That's a big number that may be overkill for your app. I would recommend you use a smaller precision, like NUMBER(18) (a long) or so.

The first and second examples are equivalent. In the third case, you are assigning an explicit name (PK_EXAMPLE_3) to the primary key constraint. In the first two cases, the PK will end up with auto-generated "ugly" names. Not a big deal, but that name may be end up being different in different environments (dev, test, staging, prod) and that can be significant sometimes, when you submit SQL scripts to be executed by third parties, or in automated ways.

Other than that, your examples are almost equivalent.