Re: simple association/ordering table
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