Обсуждение: odd error...

CREATE TABLE tmp3 (

datname VARCHAR(255),
title VARCHAR(100),
artist VARCHAR(100),
album VARCHAR(100),
mp3checksum CHAR(32),
CONSTRAINT md5
    FOREIGN KEY (mp3checksum)
    REFERENCES tcontent (mp3checksum)
    ON DELETE CASCADE
);

NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR:  UNIQUE constraint matching given keys for referenced table
"tcontent" not found

what am I doing wrong here?

"alex b." <mailinglists1@gmx.de> writes:

> CREATE TABLE tmp3 (
>
> datname VARCHAR(255),
> title VARCHAR(100),
> artist VARCHAR(100),
> album VARCHAR(100),
> mp3checksum CHAR(32),
> CONSTRAINT md5
>     FOREIGN KEY (mp3checksum)
>     REFERENCES tcontent (mp3checksum)
>     ON DELETE CASCADE
> );
>
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  UNIQUE constraint matching given keys for referenced table
> "tcontent" not found
>
> what am I doing wrong here?

Column "mp3checksum" of table "tcontent" needs a UNIQUE
constraint--does it have one?

-Doug

Doug McNaught wrote:
> "alex b." <mailinglists1@gmx.de> writes:
>
>
>>CREATE TABLE tmp3 (
>>
>>datname VARCHAR(255),
>>title VARCHAR(100),
>>artist VARCHAR(100),
>>album VARCHAR(100),
>>mp3checksum CHAR(32),
>>CONSTRAINT md5
>>    FOREIGN KEY (mp3checksum)
>>    REFERENCES tcontent (mp3checksum)
>>    ON DELETE CASCADE
>>);
>>
>>NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
>>check(s)
>>ERROR:  UNIQUE constraint matching given keys for referenced table
>>"tcontent" not found
>>
>>what am I doing wrong here?
>
>
> Column "mp3checksum" of table "tcontent" needs a UNIQUE
> constraint--does it have one?

no it doesn't - because it is possible that the same exact file might be
existent on multiple CDs...
and therefore "mp3checksum" cannot have a UNIQUE constraint.
am I right?

"alex b." <mailinglists1@gmx.de> writes:

> > Column "mp3checksum" of table "tcontent" needs a UNIQUE
> > constraint--does it have one?
>
> no it doesn't - because it is possible that the same exact file might
> be existent on multiple CDs...
> and therefore "mp3checksum" cannot have a UNIQUE constraint.
> am I right?

Then "mp3checksum" can't be a foreign key--a foreign key must be
unique in its own table.  If "tcontent" has a primary key, use that as
the foreign key in in the other table; if it doesn't, you might want
to create a SERIAL column to serve as the primary key.

-Doug

so do a seach on the web for 'surrogate keys': hint, a sequence, primary key column.

alex b. wrote:
> Doug McNaught wrote:
>
>> "alex b." <mailinglists1@gmx.de> writes:
>>
>>
>>> CREATE TABLE tmp3 (
>>>
>>> datname VARCHAR(255),
>>> title VARCHAR(100),
>>> artist VARCHAR(100),
>>> album VARCHAR(100),
>>> mp3checksum CHAR(32),
>>> CONSTRAINT md5
>>>     FOREIGN KEY (mp3checksum)
>>>     REFERENCES tcontent (mp3checksum)
>>>     ON DELETE CASCADE
>>> );
>>>
>>> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
>>> check(s)
>>> ERROR:  UNIQUE constraint matching given keys for referenced table
>>> "tcontent" not found
>>>
>>> what am I doing wrong here?
>>
>>
>>
>> Column "mp3checksum" of table "tcontent" needs a UNIQUE
>> constraint--does it have one?
>
>
> no it doesn't - because it is possible that the same exact file might be
> existent on multiple CDs...
> and therefore "mp3checksum" cannot have a UNIQUE constraint.
> am I right?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>