Re: Stuck on Foreign Keys

am Thu, dem 20.09.2007, um 14:13:40 +1000 mailte Chester folgendes:
> Hi
>
> I have a question regarding foreign keys, I just cannot get it to create
> them for me....I must be doing something wrong but I have no idea what
> that might be :)
>
> I have a table "clients"
>
> clientID (primary)
> ticode
> Firstname
> SecondName
>
> I have a second table "titles"
>
> ticode (primary)
> Title
>
> I am trying to create a foreign key on TIcode "clients" table as in below,
>
> ALTER TABLE clients ADD CONSTRAINT the_title FOREIGN KEY (ticode)
> REFERENCES titles (ticode) ;
>
> I keep getting this error
>
> ERROR: insert or update on table "clients" violates foreign key
> constraint "the_title"
> DETAIL: Key (ticode)=( ) is not present in table "titles".
>
> Sorry, I have no idea where I am going wrong...Any help would be great

my guess: Table clients, column ticode isn't a INT. It it a TEXT-type
and contains an entry ' '.

test=> create table clients (clientid serial primary key, ticode text);
NOTICE: CREATE TABLE will create implicit sequence "clients_clientid_seq" for serial column "clients.clientid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "clients_pkey" for table "clients"
CREATE TABLE
test=*> create table titles(ticode text unique, title text);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "titles_ticode_key" for table "titles"
CREATE TABLE
test=*> insert into clients values (1, ' ');
INSERT 0 1
test=*> alter table clients add constraint the_title FOREIGN KEY (ticode) REFERENCES titles (ticode);
ERROR: insert or update on table "clients" violates foreign key constraint "the_title"
DETAIL: Key (ticode)=( ) is not present in table "titles".

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net