CODEWITHSUNDEEP
oracle-database
Arve Hansen
Arve Hansen

Reputation: 1008

What is the best way of creating a unique identifier name in Oracle?

I want to create a check constraint on several thousand columns in a database, but all constraints needs a name that is unique in the database. I wanted to use a guid, but because of limitations in Oracle, the name can't be longer than 30 characters.

Here is an example of the syntax:

CREATE TABLE mytable
(
    col1 DATE 
        DEFAULT to_date('19000101', 'yyyymmdd') 
        CONSTRAINT unique_name_needed CHECK(col1 = TRUNC(col1)) 
        NOT NULL
)

Upvotes: 1

Views: 324

Answers (1)

Rene
Rene

Reputation: 10551

We use a 3 letter short name for every table. These three letters we use to name our constraints. So the short name for mytable could be mtb.

Constraint names are then:

Primary: mtb_pk
Unique: mtb_uk
Foreign: mtb_otb_fk  where otb is the short name of the other table.

The trick of course is to come up with unique short names for every table.

Upvotes: 1

Related Questions