Insert random data into a table that has foreign keys (Oracle)

Question

I have a table "usuario" (idUsuario, nome, departamentoId) and another table "departamento" (idDepartamento, nome).

I would like to enter random data in the user table, but for this, I need to consider only the Ids (Fks) of the departments that have already been entered.

I think it should be something like:

INSERT INTO usuario (idusuario, nome, departamentoId)
VALUES (seq_usuario.nextVal, 'nomealeatorio', FK_ALEATORIA_DEPARTAMENTO);

How do I generate this 'nominaleatorio' and take a department FK that already exists?

Answer 1

It is possible to query the department table and retrieve a random row to add to the record.

In that article it explains how to do this in several banks. In your case, using Oracle, the query would look like the following:

INSERT INTO usuario(idUsuario, nome, departamentoId)
SELECT seq_usuario.nextVal, 'nomealeatorio', d.idDepartamento FROM
    ( SELECT idDepartamento FROM departamento d
    ORDER BY dbms_random.value )
    WHERE rownum = 1

This way, you will insert a record into the user table with the desired sequential, random name you chose and a random department existing in the department table