Foreign key may not be primary key?

Question

I’m new to databases. My teacher asked us to link to 3 movie tables:

• Titulos, with names and link to movies
• Categorias of the film and link to the films
• A middle table that would link category to movie title.

Then, the middle table must have two primary keys for the tables Títulos and Categorias.

What he wants:

He who wants our comic allows the search of the film by name (title) and appear the category at the same time.

What will be my foreign key: the movie link or the name? Obs: the primary key is the name and not the link.

I may have a foreign key that isn’t a primary key?

Answer 1

In fact what you want to do is a link N to N between 2 tables, which is already known that it is not possible, so you do the trick of using a third table between the 2 tables that will contain the Id and the Id of another, so you would have something like this:

TABELA TITULO:
IdTitulo inteiro CHAVE PRIMARIA,
Nome string,
Link string,

TABELA CATEGORIA:
IdCategoria inteiro CHAVE PRIMARIA,
Nome string,
Link string,

TABELA TITULO_CATEGORIA:
IdTituloCategoria inteiro CHAVE PRIMARIA,
IdTitulo inteiro CHAVE ESTRANGEIRA,
IdCategoria inteiro CHAVE ESTRANGEI

RA

In the Table we have the Id of the tables Title and Category, the links will be like this:

Titulo  1---------------N TituloCategoria N-------------1 Categoria

An example of how a link looks like this (the tables are different but the case is identical to yours):

I must remind you that every foreign key is a primary key as well, and every primary key can be a foreign key from another table. You cannot say that the Title name is a foreign key because it is not a primary key. Primary keys should always be integer, DBMS tool can even allow it to be a string but this is completely wrong. Primary keys should always be values that never will repeat themselves or pass close to repeat themselves.

Answer 2

What will be my foreign key: the movie link or the name? Obs: the primary key is the name and not the link.

The right answer, considering the standards and good practices of database modeling is: none of them.

The correct is that your associative table already makes this connection of foreign keys.

A DDL of your database would be something like this (I don’t know what database technology you use, so I’m using something close to SQL ANSI):

create table Titulos {
    TituloId int primary key auto increment,
    NomeTitulo varchar(255) not null
};

create table Categorias {
    CategoriaId int primary key auto increment,
    NomeCategoria varchar(255) not null
};

create table TitulosAssocCategorias {
    TitulosAssocCategoriasId int primary key auto increment,
    TituloId int not null,
    CategoriaId not null
};

alter table TitulosAssocCategorias 
add constraint TitulosAssocCategorias_Titulo_FK foreign key (TituloId) references Titulos (TituloId);

alter table TitulosAssocCategorias 
add constraint TitulosAssocCategorias_Categoria_FK foreign key (CategoriaId) references Categorias (CategoriaId);

Having this, the following select brings the data together:

select tc.*, c.*, t.*
from TitulosAssocCategorias tc
inner join Categorias c on c.CategoriaId = tc.CategoriaId
inner join Titulos t on t.TituloId = tc.TituloId
where t.NomeTitulo like '%Título de um Filme%';

I may have a foreign key that isn’t a primary key?

No. A foreign key is necessarily a primary key from another table.