SQL SERVER - RELATIONSHIP 1: N

Question

SQL SERVER - RELATIONSHIP 1: N

Navigation

#1 by (3 votes)

2

I have two tables: books and publishers .

Their relationship was defined as follows, a book can have a publisher and a publisher can be in several books, ie 1: N.

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

But 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 with putting FK in the table editor?

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;

I brought the related data between the two tables without problems:

So what's the problem in putting FK on the table of 1? Putting in N would just be for the sake of standards, does it not interfere with the search results?

sql-server relacionamento

asked by anonymous 26.02.2018 / 22:00

1 answer

Angular Format-date with date decreased by one day How to normalize this small database?

score 3 · Accepted Answer

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

Imagine that you went to a laundry to wash your T-shirts and when you left them you filled out a registration with your personal information and what your T-shirts were.

This data has been stored as follows:

Person table:

IdPessoa | Nome
1        | HeyJoe
2        | Caique

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 t-shirts belong to IdPessoa = 1 , meaning they belong to HeyJoe .

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

Imagine that someone goes there and deletes from the database the IdPessoa=1 registration, what will happen with the blue and yellow shirts, will they stay in the store forever? No.

No, we can see that the records of people who have wash 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 that 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

AndwhatwouldhappenifItrytodeleteapersonafterdoingtherelationshipwithFK:

DELETEPessoaWHEREIDPessoa=1

Theaboveoperationwouldbecanceledandthefollowingerrorwouldbedisplayed:

TheDELETEstatementconflictedwiththeREFERENCEconstraint "FK_Pants_Pants". The conflict occurred in database "Negotiation", table "dbo.Camiseta", column 'IdPessoa'. The statement has been terminated.

Itispossibletoperformqueriesrelatingtotablesthatdonothavetheirdefinedkeys,butthatisnotwhyweshoulddothem.

TherulesexistforonereasonandthemainreasonforusingFKistoensuretheintegrityofthedata,another answer that can help you.