When to use foreign key as a primary key at the same time?

Question

I have get an intermediate table ArticleLanguage

idArticleLanguage
ArticleId
LanguageId
Name

Foreign keys are:

ArticleId
LanguageId

Should I use primary keys for:

ArticleId
LanguageId

Because these fields are primary keys in related tables?

Answer 1

Link / Junction Tables

Assuming the linked tables are defined as:

CREATE TABLE Article
(
     ArticleId INT PRIMARY KEY
     -- ... other columns
);

CREATE TABLE Language
(
    LanguageId INT PRIMARY KEY
     -- ... other columns
);

As per @JulioPérez Option 1, the link table could be created as:

CREATE TABLE ArticleLanguage
(
    ArticleId INT NOT NULL,
    LanguageId INT NOT NULL,
    Name VARCHAR(50),

   -- i.e. Composite Primary Key, consisting of the two foreign keys.
   PRIMARY KEY(ArticleId, LanguageId),
   FOREIGN KEY(ArticleId) REFERENCES Article(ArticleId),
   FOREIGN KEY(LanguageId) REFERENCES Language(LanguageId)
);

i.e. with a composite primary key consisting of the two foreign keys used in the "link" relationship, and with no additional Surrogate Key (idArticleLanguage) at all.

Pros of this approach

• Enforces uniqueness of the link, i.e. the same ArticleId and LanguageId cannot be linked more than once.
• Saves an unnecessary additional surrogate key column on the link table.

Cons of this approach:

• Any downstream tables which needs to reference this link table, would need to repeat both keys (ArticleId, LanguageId) as a composite foreign key, which would again consume space. Queries involving downstream tables which reference ArticleLanguage would also be able to join directly to Article and Language, potentially bypassing the link table (it is often easy to 'forget' that both keys are required in the join when using foreign composite keys).

SqlFiddle of option 1 here

The alternative (@JulioPérez Option 2), would be to to keep your additional surrogate PK on the reference table.

CREATE TABLE ArticleLanguage
(
    -- New Surrogate PK
    idArticleLanguage INT NOT NULL AUTO_INCREMENT,
    ArticleId INT NOT NULL,
    LanguageId INT,
    Name VARCHAR(50),

   PRIMARY KEY(idArticleLanguage),
   -- Can still optionally enforce uniqueness of the link
   UNIQUE(ArticleId, LanguageId),
   FOREIGN KEY(ArticleId) REFERENCES Article(ArticleId),
   FOREIGN KEY(LanguageId) REFERENCES Language(LanguageId)
);

Pros of this Approach

• The Primary Key idArticleLanguage is narrower than the composite key, which will benefit any further downstream tables referencing table ArticleLanguage. It also requires downstream tables to join through the ArticleLanguage link table in order to get ArticleId and LanguageId, for further joins to the Language and Article tables.
• The approach allows for an additional use case, viz that if it IS possible to add the same link to Language and Article more than once (e.g. two revisions or two reprints etc), then the UNIQUE key constraint can be removed

Cons of this Approach

• If only one unique link per Article and Language is possible, then the additional surrogate key is redundant

SqlFiddle of option 2 here

If you're asking for an opinion, I would stick with option 1, unless you do require non-unique links in your ArticleLanguage table, or unless you have many further downstream tables which reference ArticleLanguage (this would be unusual, IMO).

Table per Type / per Class Inheritance

Unrelated to OP's post, but another common occurrence where a Foreign Key can be used as a Primary Key in the referencing table is when the Table per Type approach is taken when modelling an object oriented class hierarchy with multiple subclasses. Because of the 0/1 to 1 relationship between subclass and base class tables, the base class table's primary key can also be used as the primary key for the subclass tables, for instance:

CREATE TABLE Animal
(
   AnimalId INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   -- Common Animal fields here
);

CREATE TABLE Shark
(
   AnimalId INT NOT NULL PRIMARY KEY, 
   -- Subclass specific columns
   NumberFins INT,
   FOREIGN KEY(AnimalId) REFERENCES Animal(AnimalId)
);

CREATE TABLE Ewok
(
   AnimalId INT NOT NULL PRIMARY KEY, 
   -- Subclass specific columns
   Fleas BOOL,
   FOREIGN KEY(AnimalId) REFERENCES Animal(AnimalId)
);

More on TPT and other OO modelling in tables here

Answer 2

You have 2 ways:

1) Put "ArticleId + LanguageId" as your only primary key in "intermediate table" and you can name it as "idArticleLanguage". This is called a "composite" primary key because it is composed by 2 (in other case more than 2) fields, in this case 2 foreign keys (PK= FK + FK).

2) Create "idArticleLanguage" that has no relation to the other two "id" and set it as primary key.It can be a simple auto-increment integer.

Both alternatives are accepted. Your election will depend on the goal you want to achieve because what happens if you need to add in this intermediate table the same Article with the same language (Wilkommen German for example) because you have 2 different editions of the article? if you choose alternative 1 it will throw an error because you will have the same composite primary key for 2 rows then you must choose alternative 2 and create a completely different primary key for this table

In any other case (or purpose) you can choose alternative 1 and it will work perfectly

About the change of your question title:

When use foreign key as primary key in the same time?

I will explain it with this example:

You have 2 tables: "country" and "city". "country" have all the countries of the world, "city" have all the cities of the world. But you need to know every capital in the world. What you should do?

You must create an "intermediate table" (named as "capital") that will have every capital on the world. So, we know that country have it's primary key "idcountry" and city have it's primary key is "idcity" you need to bring both as foreign keys to the table "capital" because you will need data of "city" and "country" tables to fill "capital" table

Then "capital" will have it's own primary key "idcapital" that can be a composite one "idcity+idcountry" or it can be an auto-increment integer in both cases you must have "idcity" and "idcountry" as foreign keys on your "capital" table.