Intermediate table of relationship n for n [closed]

Question

Intermediate table of relationship n for n [closed]

Navigation

#1 by (1 votes)

1

I have two tables with N to N in my database, so I should create an intermediate table that has the PK of each of those two tables.

My question is: how to enter data by correctly referencing the PKs in the middle table?

sql sql-server

asked by anonymous 26.10.2015 / 17:50

1 answer

Facebook Like Button on a Dynamically Changed Modal Print Content That Has Several Canvas

score 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 to meet your needs.

See 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