SQL one-to-many

Question

I am trying to build an SQL schema for a system where we have channels , each with id and one or more fixtures . It's hard for me to find a way to implement this one-to-many mapping. (i.e. one channel for many fixtures ). I use the H2 database engine .

I cannot have a table:

  id | fixture ----|---------- 1 | 1 1 | 2 2 | 3 CREATE TABLE channel( id INT NOT NULL PRIMARY KEY, fixture INT NOT NULL ); 

... since the PRIMARY KEY id must be UNIQUE .

Similarly, I cannot display the following:

 CREATE TABLE channel( id INT NOT NULL PRIMARY KEY, f_set INT NOT NULL REFERENCES fixtures(f_set) ); CREATE TABLE fixtures( id INT NOT NULL PRIMARY KEY, f_set INT NOT NULL ); 

... since this requires f_set be UNIQUE

I am currently implementing it as follows:

 CREATE TABLE channel( id INT NOT NULL PRIMARY KEY, f_set INT NOT NULL REFERENCES fixture_set(id) ); CREATE TABLE fixtures( id INT NOT NULL PRIMARY KEY, f_set INT NOT NULL REFERENCES fixture_set(id) ); CREATE TABLE fixture_set( id INT NOT NULL PRIMARY KEY ); 

... but that means we can have a channel with fixture_set that does not have fixtures (Not ideal) assigned.

I was wondering if you have any suggestions as to how I can approach this (or where my understanding is wrong). Thanks

Answer 1

One-to-many means that many elements (can) refer to one element. If this is one channel for many fixtures, then the fixtures should refer to the channels, and not vice versa, which means that the reference column must be in the fixtures table:

 CREATE TABLE channel( id INT NOT NULL PRIMARY KEY ); CREATE TABLE fixtures( id INT NOT NULL PRIMARY KEY, channel_id INT NOT NULL FOREIGN KEY REFERENCES channel (id) ); 

Answer 2

You can add CONSTRAINT to check it. Sorry for the lack of insertion of the fragment ... I do not know anything about the characteristics of H2.

Or you could completely avoid the concept of a toolbox. Then you just need to:

• channel table, with identifier only (plus other fields not related to this, of course)
• channel table, with channelId and fixtureId. The primary key will consist of (channelId, fixtureId)
• instrument table only if you need it.