Hello!

We have 2 tables: "Book", "Author".

It is understood that one author can be the authors of many books and one book can be written by several authors.

I guess there is a many-to-many connection. How teaches MSDN, made an intermediate table .. and then at random as starting from the fifth point ( Building a many-to-many connection ), I did not understand it.

As a result, I get that 1 book can be written by several authors, but 1 author can still only write 1 book.

I attach the code for creating tables. MS Server 2005 Standart.

CREATE TABLE Author ( AuthorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AuthorFamilyName VARCHAR(100), AuthorName VARCHAR(50), AuthorPatronymicName VARCHAR(100), AuthorFIO VARCHAR(100) ) --список издательств -- 1 книга - 1 издательство CREATE TABLE Publisher ( PublisherID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, PublisherName VARCHAR(100) ) -- Обеспечение связи многие-ко-многим (авторы и книги) CREATE TABLE AuthorsBooks ( --AuthorsBooksID INT NOT NULL PRIMARY KEY, AuthorID INT NOT NULL PRIMARY KEY, BookID INT ) --Информация о книге CREATE TABLE Book ( BookID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, BookTitle VARCHAR(100) NOT NULL, BookAuthor INT NOT NULL FOREIGN KEY REFERENCES AuthorsBooks, --ссылка на таблицу авторов BookYear INT, BookQuantityPages INT, BookPublisher INT NOT NULL FOREIGN KEY REFERENCES Publisher-- ссылка на таблицу издательства )

Those. In the intermediate table, column 2 is the book IDN and AuthorIDN, the primary key is here. I have the author.

Tell me, what am I wrong?



    2 answers 2

    I would create a cross-table not with a composite primary key, but with a surrogate, and AuthorId BookId make AuthorId and BookId with foreign keys:

     CREATE TABLE AuthorsBooks ( Id INT NOT NULL PRIMARY KEY AuthorID INT NOT NULL FOREIGN KEY REFERENCES Author(AuthorID), BookID INT NOT NULL FOREIGN KEY REFERENCES Book(BookID )

    Why is it with a surrogate key, and not with a composite key? There is no huge difference, but there is a huge (taken from here ):

    1. People often try to change the primary key in order to correct the data — the use of an immutable surrogate key solves the problem of cascading changes, since the primary key will never have to be changed — only 3 columns of data that are not stored anywhere else.

    2. Easier to write:

       select * from p, c where p.primary_key = c.foreign_key

    than

      select * from p, c where p.id1 = c.fk1 and p.id2 = c.fk2 and p.id3 = c.fk3 да и работает первый запрос быстрее.
    • Since the cross is only needed to link two tables, it seems to me that I do not need my own surrogate key there. For example, I can’t think of a query out of the box in which I would have to search for something by the surrogate key of this cross-table. In my version, you would still need to create an index on BookID, and in yours - two indexes. One for each foreign key. Therefore, in your version, I see two minuses compared to mine: 1) an extra field; 2) three indices instead of two; - uilenspiegel
    • and, by the way, remove the second primary key from the table :) - uilenspiegel
    • About the primary key, you are absolutely right! The question arose - In the last example, there are also 2 Primary keys, and the program gives an error. It turns out that there are also 2 Primary keys here. Maybe I misunderstand what? - chudo116
    • > and, by the way, remove the second primary key from the table :) ah, cant))) thanks) - DreamChild

    If the connection is many-to-many, then in the cross-table the primary key should be composite and include both fields. It would be nice to make them FOREGN KEY

     CREATE TABLE AuthorsBooks ( AuthorID INT NOT NULL FOREIGN KEY REFERENCES Author(AuthorID), BookID INT NOT NULL FOREIGN KEY REFERENCES Book(BookID), PRIMARY KEY (AuthorID, BookID) )
    • I tried your option, but an interesting picture turns out - the AuthorsBooks table refers to the Book table and vice versa. Those. it is impossible to create first neither table nor It refers to the other. How can you get out of this situation? - chudo116
    • Nothing special. First, the tables Book and Author are created. Then the link table is created: CREATE TABLE AuthorsBooks (AuthorID INT NOT NULL, BookID INT NOT NULL, PRIMARY KEY (AuthorID, BookID)) ) REFERENCES Author (AuthorID) ALTER TABLE AuthorsBooks CHECK ADD CONSTRAINT FK2 FOREIGN KEY (BookID) REFERENCES Book (BookID) renegator