Create a intermediary table

Databases
1

I have 2 tables with a many to many relationship (assets & claims) So I created a go between table to fix this.
The table has three things (A primary key, the primary keys of both the assets & claims table) So how do I create the table…, im thinking…

CREATE TABLE Assets_Claims (
ID int not null,
assetID int,
claimID int,
PRIMARY KEY (ID)
//how do I make both link to each table?

Thanks…

2

How about

CREATE TABLE Assets_Claims (
assetID int,
claimID int,
PRIMARY KEY (assetID, claimID)

1 Like
3

CREATE TABLE Assets_Claims ( assetID INTEGER NOT NULL , claimID INTEGER NOT NULL , PRIMARY KEY ( assetID, claimID ) , INDEX claim_assets ( claimID, assetID ) , FOREIGN KEY ( assetID ) REFERENCES assets ( assetID ) , FOREIGN KEY ( claimID ) REFERENCES claims ( claimID ) );

2 Likes
5

What is the reason you made an index of both the foreign keys, and is it ok that we dont have a PK for this table?

6

the INDEX on ( claimID , assetID ) so that you can look up all assets for a particular claim efficiently

dude, look again

7

I think he means, an auto-incremental primary key

8

thanks for giving it away… i wanted him to get that “aha!” moment when he realizes that a PK doesn’t have to be an auto_increment

~so~ important to learn what a primary key is actually supposed to do

1 Like
9

You’re welcome :shifty:

10

Unfortunately many people don’t know what a primary key is supposed to do. They add auto increment’s willie nillie and think that’s expected.

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.