Problem solve Get help with specific problems with your technologies, process and projects.

Inserting and searching many-to-many relationships

How can I insert data into a table that has composite primary key or many-to-many relationships? And are there key points in implementing search criteria?

A composite primary key is one such as this:

create table users
( id    smallint not null primary key 
, name  varchar(9)
create table projects
( id    smallint not null primary key 
, name  varchar(9)
create table userprojects
( userid  smallint not null 
, projectid smallint not null 
, projectrole varchar(15) 
, primary key (userid,projectid)
, foreign key (userid) 
    references users (id)
, foreign key (projectid) 
    references projects (id)

The userprojects table has a composite primary key.

Inserting data into a relationship table like this is no different from inserting data into any other table. You simply use an INSERT statement with VALUES. The values might come from a form such as this:

User: Role: Project:

The insert would look like this:

  into userprojects
     ( userid 
     , projectid 
     , projectrole 
     ( 3        
     , 2
     , 'Gopher'

In this example, 3 is Harry's id from the users dropdown, 2 is the Ongoing Maintenance project id from its dropdown, and Gopher is Harry's role as entered into the text box.

As for searching, the search queries will usually require a three-way join. Space does not permit a full exploration of all the different searches that are possible: projects with no users, users on more than one project, project leaders working on other projects, and so on.

There is, however, one important point about searching a many-to-many relationship table. The composite PK gives you an index, and indexes improve performance. But in the composite index, one of the two columns is in first position, meaning that searches for specific values of that column are efficient, but not the second column. Searches which involve a specific value "in the other direction" (if you know what I mean) require an additional index on the relationship table in order to improve performance. This additional index can be either just the second column, or a composite index with the second column first and then the first after it.

Dig Deeper on Oracle and SQL