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

Question

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

Navigation

#1 by (2 votes)

1

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 department IDs (FKs) 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 'nomealeatorio' and get a department FK that already exists?

sql oracle10g

asked by anonymous 03.05.2014 / 03:12

1 answer

C language, matrix exercise [closed] Show a div for each option in a dropdown

score 2 · Accepted Answer

You can refer to the department table and retrieve a random line to add to the registry.

In this article it explains how to do this across multiple banks. In your case, using Oracle, the query would look something like this:

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

In this way, you will insert a record in the user table with the desired sequence, the random name you chose, and a random department in the department table