How to create composite primary key in SQL?

Asked

Viewed 16,357 times

4

I wonder how to create a composite primary key in a weak relationship.

I mean, I create the two relationship tables and I don’t know how to "pull" the primary key to the weak table. I must pull it as foreign key before and only then create composite key?

That would be it, for example?

Create Table TabelaForte(
   Idforte Integer Identify(1,1) not null,
   Nome Varchar(50) not null,
   Descrição Varchar(50) not null,
)

Create Table TabelaFraca(
   Idfraco Integer Identify(1,1) not null,
   Atributo1 Varchar(50) not null,
   Atributo2 Varchar(50) not null,
)

ALTER TABLE TabelaForte
ADD (PRIMARY KEY (Idforte));

ALTER TABLE TabelaFraca
ADD (FOREIGN KEY(Idforte) REFERENCES TabelaForte);

ALTER TABLE TabelaFraca
ADD CONSTRAINT chave_composta PRIMARY KEY CLUSTERED (Idforte, Idfraco)
  • You are using which database?

2 answers

1

The purpose of a weak entity is to represent an entity that needs another entity to exist (Generally cardinality is One-To-Many), so the weak entity will always be composed key:

Create Table TabelaForte(
   Idforte Integer Identify(1,1) not null,
   Nome Varchar(50) not null,
   Descrição Varchar(50) not null,
)

Create Table TabelaFraca(
   Idforte Integer not null,
   Idfraco Integer Identify(1,1) not null,
   Atributo1 Varchar(50) not null,
   Atributo2 Varchar(50) not null,
)

ALTER TABLE TabelaForte
ADD (PRIMARY KEY (Idforte));

ALTER TABLE TabelaFraca
ADD (FOREIGN KEY(Idforte) REFERENCES TabelaForte);

ALTER TABLE TabelaFraca
ADD CONSTRAINT chave_composta PRIMARY KEY CLUSTERED (Idforte, Idfraco)

The only thing missing was adding the field IdForte in TabelaFraca. It is necessary to review this Identify(1,1) may not work with composite key, do not know to which database this command belongs

If you have incorrectly typed Identity(1,1) then you are using SQL Server and Identity will not work with a composite key. You will probably have to insert with Null, or in your case 0 and update the value in an Insteadof Rigger

0

If you have two tables, the Idforte actually needs to be referenced as foreign key (Foreign Key) in TabelaFraca to then be used as a Composite Key, because until now it does not exist in the TabelaFraca.

I believe that there is a missing table that links these two tables in a standardized way and easier to understand, for yourself.

Create Table TabelaMedia(
   Idfraco Integer Identify(1,1) not null,
   Idforte Integer Identify(1,1) not null,
   Atributo1 Varchar(50) not null,
   Atributo2 Varchar(50) not null,
)

constraint chave_estrangeira1 foreign key (Idfraco ) references TabelaFraca(Idfraco ),       
    constraint chave_estrangeira2 foreign key (Idforte ) references TabelaForte(Idforte )
);
  • 1

    There must be no middle table, the purpose of the weak entity is to depend on another entity to exist

Browser other questions tagged

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