CODEWITHSUNDEEP
sqloracle-databasenullableoracle12csql-view
Anders
Anders

Reputation: 8577

Generate unique ID that is NOT NULL in a Oracle view with LEFT JOIN?

I have two tables, foo and bar, that are related to each other with a one-to-many relation:

CREATE TABLE foo (
  id NUMBER PRIMARY KEY
);

CREATE TABLE bar (
  id NUMBER PRIMARY KEY,
  foo NUMBER NOT NULL,
  CONSTRAINT fk_bar_foo FOREIGN KEY (foo) REFERENCES foo (id)
);

Now I want to create a view with a LEFT JOIN between the two tables, where every row in the view has a unique id, so I tried this:

CREATE OR REPLACE my_view AS
SELECT ORA_HASH(foo.id || '~' || bar.id) id
FROM foo
LEFT JOIN bar ON bar.foo = foo.id

My problem is that the column id of the view becomes nullable, even though it could never have the value NULL. For this to work with another application, I need to have a unique non-nullable id in the view.

Can I somehow fool Oracle into making the column non-nullable? Or is there some other way to generate a unique id that is non-nullable?

Upvotes: 1

Views: 2076

Answers (1)

Jodevan
Jodevan

Reputation: 750

What about instead of building your id from the ids of the two existing tables, build it based on sys_guid()? Something like:

CREATE OR REPLACE view my_view AS
SELECT sys_guid() id
FROM foo
LEFT JOIN bar ON bar.foo = foo.id

Upvotes: 1

Related Questions