question

SQL_Junior avatar image
SQL_Junior asked

Understanding Junction Tables

Hi so let's say i created two tables Entities and Categories as you can see below

CREATE TABLE [dbo].[Entities]

(

[EntityId] INT NOT NULL,

[Title] NVARCHAR(50) NULL NULL

Constraint PK_Entities PRIMARY KEY (EntityId),

)

CREATE TABLE [dbo].[Categories]

(

[CategoryId] INT NOT NULL,

[Title] NVARCHAR(30) NULL,

Constraint PK_Categories PRIMARY KEY ([CategoryId])

}

Now enitity1 can relate category1 and category2 and at the same time entity2 can also relate to category1 which means the relation is many to many

So i will create a junction table CategoryEntitiy

CREATE TABLE [dbo].[CategoryEntitiy]

(

[EntityId] INT NOT NULL ,

[CategoryId] INT NOT NULL,

Constraint FK_CategoryEntitiy_Categories FOREIGN KEY(CategoryId) references Categories(CategoryId),

Constraint FK_CategoryEntitiy_Entities FOREIGN KEY(EntityId) references Entities(EntityId )

)

First Question, should i make them primary and foreign at the same or is foreign enough? and why?

Second Question: Do i make junction tables when there is many relationship on both tables, regardless it it was 0 to many, 1 to many or many to many? And is there another reason?

sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.