Re: simple association/ordering table

From: Lemming <thiswillbounce_at_bumblbee.demon.co.uk>
Date: Wed, 24 Mar 2004 22:43:52 +0000
Message-ID: <f93460ttcnufbflbd9radhefn9lhts00mn_at_4ax.com>


On Wed, 24 Mar 2004 23:01:19 +0100, rollingstone <rollingstone_at_email.com> wrote:

>Hi all,
>I am working on a small application to allow the user to group photos in
>an album with the functionalities to order the photos within each album
>and to associate each photo to more albums (many-to-many relationship).
>
>Here are the two tables to store the albums and the photos (I've removed
>some columns for simplicity reasons):
>
>CREATE TABLE Album (
> AlbumID INTEGER NOT NULL,
> AlbumName VARCHAR2(20) NOT NULL,
> CONSTRAINT PRIMARY KEY (AlbumID)
>);
>
>CREATE TABLE Photo (
> PhotoID INTEGER NOT NULL,
> PhotoName VARCHAR2(20) NOT NULL,
> CONSTRAINT PRIMARY KEY (PhotoID)
>);

I'm not sure what your question is.

If it's "How do I implement the many-to-many relationship between these two tables whilst enforcing referential integrity?" you need another table:

CREATE TABLE AlbumPhoto (

   AlbumId      INTEGER NOT NULL
  ,PhotoId      INTEGER NOT NULL
  ,CONSTRAINT PRIMARY KEY (AlbumId, PhotoId)
  ,FOREIGN KEY (AlbumId) REFERENCES Album (AlbumId)   ,FOREIGN KEY (PhotoId) REFERENCES Photo (PhotoId) );

[Usual disclaimer applies: I haven't tested this query, there may be syntax errors]

Lemming

-- 
Curiosity *may* have killed Schrodinger's cat.
Received on Wed Mar 24 2004 - 23:43:52 CET

Original text of this message