Stuck on Foreign Keys

On Wednesday 19 September 2007, Chester wrote:

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".

This foreign key constraint that every instance of clients.ticode must have a
corresponding (unique) titles.ticode. But you don't - there are records in
clients where there's a ticode value that's not found in titles.ticode.

Cheers!

-Ben

--
I kept looking for somebody to solve the problem.
Then I realized - I am somebody.
-- Author Unknown

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Sep 19, 2007, at 23:13 , Chester wrote:

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

You've shown us the errors but not the commands you used that threw
the errors. Show us the exact commands you used along with the errors
so we might see what's going wrong. It looks like you're not
providing a value for ticode when inserting or updating clients, but
that's just a guess.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann wrote:

On Sep 19, 2007, at 23:13 , C wrote:

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

You've shown us the errors but not the commands you used that threw
the errors. Show us the exact commands you used along with the errors
so we might see what's going wrong. It looks like you're not providing
a value for ticode when inserting or updating clients, but that's just
a guess.

Michael Glaesemann
grzm seespotcode net

Thanks Michael

The table's are existing populated table the error was happening
whilst altering the client table to add the FC, sorry if that was not
clear. Maurice put me right there were a couple of rows in the client
table with null values in the ticode column.

Thanks again guys

Cheers

C

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

On 20/09/2007, Chester <chester@hica.com.au> wrote:

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

The ticode that you are trying to insert into clients -- does this
ticode exist in the table titles?