1

Does Oracle 19 database support UUID columns, to store for example 123e4567-e89b-12d3-a456-426614174000?

I know that Oracle NoSQL supports this, but I'm looking for Oracle SQL.

If not how should I implement it? A 36 character varchar2 column with constraint to check for the value?

user5507535
  • 1,580
  • 1
  • 18
  • 39
  • 2
    [Possibly related?](https://stackoverflow.com/q/13610381/266304) – Alex Poole Oct 14 '21 at 15:04
  • use string as oracle defines it as subtype of string https://docs.oracle.com/en/database/other-databases/nosql-database/21.1/sqlreferencefornosql/using-uuid-data-type.html – nbk Oct 14 '21 at 15:05
  • Might be better asked on sister site: https://dba.stackexchange.com/ – Basil Bourque Oct 14 '21 at 15:06
  • 1
    You do realize that the example you gave is NOT a UUID, right? What are letters like `z` and `r` doing in a UUID? Rather, if you are asking about a UUID data type (as distinct from just "string"), you would want some constraints that would NOT allow a string like that to be stored in a UUID column. –  Oct 14 '21 at 15:51
  • The recommendation is to use `raw` for that –  Oct 14 '21 at 17:08
  • 3
    @nbk: you are linking to the manual of the "Oracle NoSQL" - that's something different than the Oracle database (which is a relational database) –  Oct 14 '21 at 17:10

1 Answers1

3

As the comments mention, while UUID isn't a supported type, you can easily implement a UUID column like this:

CREATE TABLE example
(
  uuid CHAR(36) CHECK (REGEXP_LIKE(uuid, '^[0-9a-f]{8}-([0-9a-f]{4}-){3}[0-9a-f]{12}$'))
);

Here is a DBFiddle showing the check constraint in action (LINK)

Del
  • 1,529
  • 1
  • 9
  • 18
  • 1
    Perhaps a bit more elegant would be to use the `[:xdigit:]` character class instead of `[0-9a-f]`. –  Oct 14 '21 at 17:23
  • @mathguy The start of string and end of string anchors aren't really necessary as the pattern takes up all 36 characters of the fixed length 36 character column. However, I do like the idea of using the character class instead of defining valid characters. – Del Oct 15 '21 at 14:06
  • 2
    Good point, I missed the data type you used. For the regex engine, though, it is still much more efficient to include the anchors, even if logically they are not required. The regex engine is not smart enough to make that logical deduction, and if a match starting at the first position is not found, it will still try to find a match starting at the second position, at the third, ... Adding the anchors (at least the starting one) helps the regexp engine avoid these wasteful checks. –  Oct 15 '21 at 14:39
  • @mathguy Good point. That makes sense. – Del Oct 15 '21 at 14:42