Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: