Q

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:

insert
  into userprojects
     ( userid 
     , projectid 
     , projectrole 
     )
values
     ( 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.


This was first published in June 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close