How to create composite primary key in SQL?

Question

How to create composite primary key in SQL?

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I would like to know how to create a composite primary key in a weak relationship.

I mean, I create the two relationship tables and I do not know how to "pull" the primary key for the weak table. Should I pull it as foreign key before and only then create the composite key?

Would that be, 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)

sql

asked by anonymous 13.10.2015 / 20:23

2 answers

Send POST with Ajax and CodeIgniter Using JSON in C # and adding points in GMAPS

score 1 · Answer 1

The purpose of a weak entity is to represent an entity that needs another entity to exist (usually the cardinality is One-To-Many), so the weak entity will always be a composite 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 to add the IdForte field to TabelaFraca . It is necessary to revise this Identify(1,1) may not work with compound key, I do not know which database this command belongs to

If you typed Identity incorrectly (1,1) then you are using SQL Server and Identity will not work with compound key. Probably you will have to insert with Null, or in your case 0 and update the value in a RatherOF trigger

score 0 · Answer 2

If you have two tables, Idforte actually needs to be referenced as foreign key (Foreign Key) in TabelaFraca and then yes it will be used as a Composite Key, because so far it does not exist in the structure of TabelaFraca .

I think there is a missing table that links these two tables in a standardized and easy-to-understand way, 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 )
);