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

Asked

Viewed 2,201 times

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 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?

1 answer

2


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

  • 1

    It worked! Thank you so much! Abs.

  • 2

    Using the package dbms_random, you can generate random numbers and strings, useful if you need to generate different "names" for some test.

  • 1

    http://www.oracle-base.com/articles/misc/dbms_random.php

Browser other questions tagged

You are not signed in. Login or sign up in order to post.