Обсуждение: Stuck on Foreign Keys
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 TIA C .
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?
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.