How to exchange the primary key of a table in Mysql

Question

CREATE TABLE Produto (
  Codigo_Produto INTEGER,
  Descricao_Produto VARCHAR(50),
  Preco_Produto FLOAT,
  PRIMARY KEY (Codigo_Produto)
);

CREATE TABLE Nota_fiscal (
  Numero_NF INTEGER,
  Data_NF DATE,
  Valor_NF FLOAT,
  PRIMARY KEY (Numero_NF)
);

CREATE TABLE Itens (
    Produto_Codigo_Produto INTEGER,
    Nota_fiscal_Numero_NF INTEGER,
    Num_Item INTEGER,
    Qtde_Item INTEGER,
        PRIMARY KEY(Produto_Codigo_Produto, Nota_fiscal_Numero_NF),
    FOREIGN KEY (Produto_Codigo_Produto) REFERENCES Produto(Codigo_Produto),
    FOREIGN KEY (Nota_fiscal_Numero_NF) REFERENCES Nota_fiscal(Numero_NF)
);

I have a problem, I have an exercise q have to create these 3 tables, but then I have to perform the following action: "Make the primary key of the Items table no longer composed by code of the product. more number of the invoice and become Num_item" However, when I try to run the following code:

ALTER TABLE Itens DROP PRIMARY KEY;

ALTER TABLE Itens
ADD PRIMARY KEY (Num_Item);

mysql returns this error: "Error Code: 1025. Error on Rename of './sales/#sql-1_11' to './sales/items' (Rrno: 150 - Foreign key Constraint is incorrectly Formed) "

Does anyone know what it can be?

Answer 1

The problem is you’re using the field that is Primary key also as Foreign key, and gives error there.

You will see the error if you try to drop/add a single command like this:

ALTER TABLE Itens DROP PRIMARY KEY, ADD PRIMARY KEY (Num_Item);

The mistake will be:

Cannot drop index 'PRIMARY': needed in a Foreign key Constraint

In short, you cannot remove a primay key which is being used also as Foreign key.

To make the drop of Primary key, first need to remove the Foreign key, then it takes more work that. Assuming the model is correct (the two Foreign Keys), then you will also need to delete before. However, to exclude a Foreign key you need to know the name of Constraint (every key is at the end a Constraint).

Whoever devised this exercise may not have planned it well, but it is another matter... so you first need to change the creation of the keys, using CONSTRAINT, so that you can give it a name, and it’s easy to delete after:

CREATE TABLE Itens (
    Produto_Codigo_Produto INTEGER,
    Nota_fiscal_Numero_NF INTEGER,
    Num_Item INTEGER,
    Qtde_Item INTEGER,
    PRIMARY KEY(Produto_Codigo_Produto, Nota_fiscal_Numero_NF)
);

-- ADICIONA AS DUAS FOREIGN KEYS
ALTER TABLE Itens ADD CONSTRAINT fk_Codigo_Produto FOREIGN KEY (Produto_Codigo_Produto) REFERENCES Produto(Codigo_Produto);
ALTER TABLE Itens ADD CONSTRAINT fk_Numero_NF FOREIGN KEY (Nota_fiscal_Numero_NF) REFERENCES Nota_fiscal(Numero_NF);

-- REMOVE AS DUAS FOREING KEYS, DEPOIS A PRIMARY KEY E RECRIA
ALTER TABLE Itens DROP FOREIGN KEY fk_Codigo_Produto;
ALTER TABLE Itens DROP FOREIGN KEY fk_Numero_NF;
ALTER TABLE Itens DROP PRIMARY KEY, ADD PRIMARY KEY (Num_Item);

Note that, with ADD CONSTRAINT we can give names, for example "fk_Codigo_Product" and then it is easy to delete, the same could be done with the Primary key :)

You can see an example working here: http://sqlfiddle.com/

A note for those who see this answer: it is an exercise, in a real case, in recreating a Primary key, that as I mentioned is a Constraint, the validation will be applied, that is, there were 2 fields and became 1, if there are records with duplicate values it will not be possible to create the key. The most common way of doing in such cases is:

• Create a new table in the correct structure;
• Insert data into this new table (SELECT INTO/INSERT FROM SELECT);
• Delete the old table;
• Rename the new table.