Diffraction sets a limit to the depth of field. In the ideal case of a theoretically perfect pinhole camera ignoring diffraction everything is in perfect focus. Unfortunately, it is not possible to make a perfect pinhole camera and diffraction also creeps into the smaller aperture.

With respect to the circle of confusion, the smaller the aperture, the smaller the circle of confusion. However, diffraction plays a roll in this also and starts taking its toll on the sharpness that is gained by reducing the aperture.

When a beam of light (parallel light waves) passes through the aperture of a camera it spreads out some. The smaller the aperture, the more it spreads out.

<warning type="math">

The field strength of the beam as its spreads out relative to the axis is:
2 J1(x)/x { x = 2 φ π R / &lamda; }
R is the radius of the aperture, &lamda; is the wavelength of the light and J1 is the first order Bessel function.

The first intercept for this is at 1.21967 &lamda;/(2 R). This number is the angle from the axis relative to the wavelength of light over the diameter of the aperture. The area between 0 and 1.21967 contains 84% of the power of the light beam (close enough for government work).

Unfortunately, within photography all visible frequencies of light are used. For simplicity only the yellow-green at 555 nm that human eyesight has maximum sensitivity to is used. From this, the diameter of the size of the diffraction pattern is (remembering to multiply the radius above by 2 to get the diameter)
2 * R * 1.21967 * 555nm
Thus, we get 1353.8337 nm or 0.0013538337 mm times the aperture diameter.

What just happened there? Wouldn't the larger aperture on a longer lens mean less diffraction?
The focal length does not play a role in the diffraction. Or rather, use of the f-stop is the ratio of the focal length to the aperture already takes this into account. A shorter lens with a smaller aperture (but same ratio) spreads out just as much as a longer lens with a larger aperture. The amount of diffraction at the plane of the film is the same for a 200mm f/4 and a 20mm f/4.
As mentioned elsewhere, the accepted value for the circle of confusion is about 0.03 mm. To solve for the smallest aperture (largest R) we divide the 0.0013538337 mm into 0.03 mm and get 22.159. At f-stop 22, the amount of diffraction is about the size of the largest accepted circle of confusion.

</warning>

Now, for the most part, this doesn't mean too much when dealing with normal photographic situations. When taking a picture of the landscape where you want everything to be in focus this doesn't play that great a role. Sure, its a little fuzzy but acceptably so - you don't expect to be able to resolve the leaves on a tree a mile away.

However, this does play a role in special situations. Most notably, macro photography with extension tubes. When magnifying a specific part of the image on the lens all of the errors are magnified. This is everything from lint on the front element to aberrations in the lens itself. As the effective aperture changes, so does the effects of diffraction. Photographing at f/22 and losing two stops as part of magnification the diffraction equations deal with an f/45 aperture instead.

Realize that at 2x magnification, diffraction is easily visible at f/16 and smaller. Nikon recommends not to use an effective f-stop of f/11 when photographing with extension tubes. The calculation for effective aperture is:
Effective Aperture = (Magnification + 1) * Aperture Indicated on Lens

http://www.photo.net/photo/macro.html http://www.photo.net/photo/optics/lensTutorial.html (heavly used)
http://www.photo.net/bboard/q-and-a-fetch-msg?msg_id=000cJx
The Nikon Field Guide