Relationship intermediate table n for n

Asked

Viewed 1,949 times

1

I have two tables with relation N to N in my database, with this, I must create an intermediate table that has the PK of each of these two tables.

My doubt is: how to insert data referencing the Pks correctly in the intermediate table?

sql sql-server
  • 4

    Explain better which way you want to enter the data, via insert sql? Do you already have the PK you want to insert? .

1 answer

1

You can create the primary key with the combination of the keys of the tables involved and the rest of the fields get your need.

take the example.

    CREATE TABLE [dbo].[Table_x](
        [idtabelax] [int] IDENTITY(1,1) NOT NULL,
        [descricao] [nchar](10) NULL,
     CONSTRAINT [PK_Table_x] PRIMARY KEY CLUSTERED 
    (
        [idtabelax] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO



    CREATE TABLE [dbo].[Table_Y](
        [idtabelaY] [int] IDENTITY(1,1) NOT NULL,
        [descricao] [nchar](10) NULL,
     CONSTRAINT [PK_Table_Y] PRIMARY KEY CLUSTERED 
    (
        [idtabelaY] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


CREATE TABLE [dbo].[Table_intermediaria](
    [idtabelaX] [int] NOT NULL,
    [idtabelaY] [int] NOT NULL,
    [descricao] [nchar](10) NULL,
 CONSTRAINT [PK_Table_intermediaria] PRIMARY KEY CLUSTERED 
(
    [idtabelaX] ASC,
    [idtabelaY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_intermediaria]  WITH CHECK ADD  CONSTRAINT [FK_Table_intermediaria_Table_x] FOREIGN KEY([idtabelaX])
REFERENCES [dbo].[Table_x] ([idtabelax])
GO

ALTER TABLE [dbo].[Table_intermediaria] CHECK CONSTRAINT [FK_Table_intermediaria_Table_x]
GO

ALTER TABLE [dbo].[Table_intermediaria]  WITH CHECK ADD  CONSTRAINT [FK_Table_intermediaria_Table_Y] FOREIGN KEY([idtabelaY])
REFERENCES [dbo].[Table_Y] ([idtabelaY])
GO

ALTER TABLE [dbo].[Table_intermediaria] CHECK CONSTRAINT [FK_Table_intermediaria_Table_Y]
GO

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.