How to duplicate a key’s value in SQL?

Asked

Viewed 333 times

1

I need the keys Process and Numero_revisao, can receive values that already exist in the table. for example:

Id = 1, Numero_Processo = 1, Numero_Revisao = 1
Id = 2, Numero_Processo = 1, Numero_Revisao = 2
Id = 2, Numero_Processo = 1, Numero_Revisao = 3

or 

Id = 4, Numero_Processo = 2, Numero_Revisao = 1
Id = 2, Numero_Processo = 2, Numero_Revisao = 2
Id = 2, Numero_Processo = 2, Numero_Revisao = 3

In both cases I need to repeat the values Numero_process and Numero_revision. These fields are keys in other tables with the Quality table .

inserir a descrição da imagem aqui inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

sql query
  • 3

    From what I understand you actually need to use a composite key (id, numero_process, numero_revise). First you need to see if you are like identity your current key.

  • That one id also need to repeat? It is the table pk?

  • A foreign key necessarily needs to be a unique key in the reference table. You will need to choose another key from the table that is unique (id, numero_processo, numero_revisao, for example) and change your foreign key to this unique new record.

  • My Id is unique, no need to repeat. But the fields Numero_process and Numero_revisao I need it to be key and accept repeated values. How to do?

  • It has to be a terrifying key, not a primary key, remove PK_Numero_Processo and PK_Numero_Revisao

  • "My Id is unique, no need to repeat. But the fields Numero_process and Numero_revision I need it to be key and accept repeated values" @Daniellearrudatorres the table can only have a primary key if the field ID already is, you can use the other fields as foreign keys and repeat without problem

  • 1

    @Rovannlinhalis is right, it is not a primary key obliged to observe, remove the comment that was purposeless

Show 2 more comments

1 answer

0

The problem seems to be that it is repeating the same value in the Id field and not in the Process and Number fields. You really can’t have a PK anymore, but you can have a PK composed.

There are three possible scenarios:

  • You use a different id for each line and keep the PK as it is, in which case it would accept.

  • If it is possible to repeat the Id, but never repeat the combination of the three fields, in this case I would make a composite PK.

    ALTER TABLE [Quality_insp_inj_process] ADD CONSTRAINT [Pk_quality_insp_inj_process] PRIMARY KEY (Id, Numero_process, Numero_review)

  • Modeling your tables in a different way, in this case it would be necessary to know the project better to be able to suggest these changes.

Worth the/

Browser other questions tagged sql query

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