Composite primary key using a foreign key

Asked

Viewed 5,860 times

1

It is possible to create a composite primary key using a foreign key?

I have the table mov_estoque, where I thought to put as composite key the id_mov and id_prod, however, id_prod is a foreign key of the products table.

tried:

ALTER TABLE mov_estoque
DROP PRIMARY KEY, ADD PRIMARY KEY (id,id_pro);

but I get:

1832 - Cannot change column 'id_pro': used in a Foreign key Constraint 'mov_estoque_ibfk_2'

mysql database

  • Why does it have to be composed? Perhaps a drive table and an auxiliary relating n to n drive-products would be more cohesive in the model, so vc would have separate drive and products of this drive

1 answer

3


ALTER TABLE NomeDaTabela
ADD CONSTRAINT PK_NomeDaPK PRIMARY KEY CLUSTERED (Coluna1, Coluna2)

If you want to add PK during table creation, use this syntax:

CREATE TABLE NomeDaTabela (
   Coluna1 INT,
   Coluna2 INT,
   CONSTRAINT PK_NomeDaPK PRIMARY KEY (Coluna1, Coluna2)
)

Browser other questions tagged mysql database

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