SQL SERVER - RELATIONSHIP 1:N

Question

I have two tables: books and publishers.

Their relationship was defined as follows, a book may have a publishing house and a publishing house may be in several books, i.e., 1:N.

I learned that the foreign key (FK) must be in table N, in case, in table books.

However, I did a test and put it in table 1 (publishers). If this key is only used to establish a relationship between the tables, so that it is possible to search for related data between them, what is the problem putting FK in the publishing table?

Below is my test:

CREATE TABLE Editoas(
    IdEditora INT PRIMARY KEY IDENTITY,
    FkLivro INT,
    NomeEditora VARCHAR(500),
    EmailEditora VARCHAR(500),
    FOREIGN KEY(FkLivro) REFERENCES Livros(IdLivro)
    );

SELECT * FROM Livros AS L, Editoras AS E
    WHERE L.IdLivro = E.FkLivro;

Brought the related data between the two tables smoothly:

So what’s the problem in putting FK in the table of 1? Putting N would be only a matter of standards, does not interfere with the result of the searches?

Answer 1

To answer your question in a more didactic way, I’ll take a simpler example of a relationship 1:N.

Imagine that you went to a laundry (shop) to wash your T-shirts and at the time of leaving them you filled out a registration with your personal data and what were your T-shirts.

These data have been stored as follows:

Table Person:

IdPessoa | Nome
1        | HeyJoe
2        | Caique

Table T-shirt:

IdCamiseta | Descricao | IdPessoa
1          | Azul      | 1
2          | Amarela   | 1
3          | Preta     | 2

In the above example it is clear that the blue and yellow shirts belong to the person of IdPessoa = 1, that is, belong to the HeyJoe.

We know that T-shirts must have a "owner", a person.

Imagine someone goes in there and erases the person’s record from the database IdPessoa=1, What will happen to the blue and yellow T-shirts, will stay in the store forever? No.

No, we may own that the records of people who own washing T-shirts are erased to ensure the integrity of the information. For this we must use the foreign keys that will accuse an error when we try to delete a person who owns t-shirts.

See in code:

CREATE TABLE Pessoa(
    IdPessoa INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Nome VARCHAR(20) NOT NULL
)

CREATE TABLE Camiseta(
    IdCamiseta INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Descricao VARCHAR(20) NOT NULL,
    IdPessoa INT NOT NULL 
    CONSTRAINT FK_Camiseta_Pessoa FOREIGN  KEY(IdPessoa) REFERENCES Pessoa(IdPessoa)
)

INSERT INTO Pessoa VALUES ('HeyJoe')
INSERT INTO Pessoa VALUES ('Caique') 


INSERT INTO Camiseta VALUES ('Azul', 1)
INSERT INTO Camiseta VALUES ('Amarela', 1)
INSERT INTO Camiseta VALUES ('Preta', 2)

SELECT * FROM Pessoa, Camiseta WHERE Pessoa.IdPessoa = Camiseta.IdPessoa

And what would happen if I tried to delete a person after doing the relationship with FK:

DELETE Pessoa WHERE IDPessoa = 1

The above operation would be canceled and following error would be displayed:

The DELETE statement conflicted with the REFERENCE Constraint "Fk_camiseta_pessoa". The Conflict occurred in database "Trading", table "dbo. T-shirt", column 'Idpessoa'. The statement has been terminated.

It is possible to carry out queries relating tables that do not have their defined keys, but that is not why we should do them.

The standards exist for one reason and the main reason for the use of FK is to ensure data integrity, follows another reply who can assist you.