多对多关系的典型设计就是图书表和作者表,一本图书可以有多个作者,而一个作者可以写多本图书,两者的关系通过一张中间外键表维护
--创建基表
CREATE TABLE [dbo].[Book] --图书表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Book] ADD CONSTRAINT [PK_dbo.Book] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Author] --作者表
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Author] ADD CONSTRAINT [PK_dbo.Author] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BookAuthor] --图书作者关联表
(
[Book_ID] [int] NOT NULL,
[Author_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BookAuthor] ADD CONSTRAINT [PK_dbo.BookAuthor] PRIMARY KEY CLUSTERED ([Book_ID], [Author_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Book_ID] ON [dbo].[BookAuthor] ([Book_ID]) ON