How to prevent the creation of records where one of the fields has the value already registered?

Asked

Viewed 63 times

-2

Create Table genero_filme(
NomeF varchar2(30) NOT NULL,
genero varchar2(20) NOT NULL,
--Restrições de intregridade 
--1. Não existe gêneros diferentes com o mesmo nome
    Constraint fk_Filme_g FOREIGN Key(NomeF)
        References Filme(NomeF),

How do I not have different genres with the same name?

sql oracle

1 answer

4

Create a UNIQUE composed by (Name, gender) .

Create Table genero_filme(
NomeF varchar2(30) NOT NULL,
genero varchar2(20) NOT NULL,
--Restrições de intregridade 
--1. Não existe gêneros diferentes com o mesmo nome
Constraint genero_unk UNIQUE (NomeF,genero) -- Nova unique key
Constraint fk_Filme_g FOREIGN Key(NomeF)
References Filme(NomeF),
--(...)

If the goal is that the table genre have unique gender names, I suggest following template (just one example):

Modelo de dados exemplo

  • 1

    But if you put UNIQUE only in the field genero how are you going to register different films of the same genre? It shouldn’t be: UNIQUE (NomeF, genero)?

  • I made the change in the answer, did not understand that 'different with the same name? ' referred to the name of the film and not the name of Genre.

  • Not correct because I want to be able to put some generos but I do not want to be able to introduce the same kind

  • From my point of view, the gender table should not have Constraint fk_Filme_g FOREIGN Key(Nomef), and create a link table liga_filme_genero with the PRIMARY KEY table of films and gender table.

  • 1

    @user171371: what you want is not to prevent the same pair (film, genre) can be inserted in duplicate? If you put UNIQUE (NomeF, genero) a film may be associated with several genres and a genre may be associated with several films. What cannot be included are two equal pairs.

Browser other questions tagged sql oracle

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