Review your favorite Linux distribution.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!


  Search this Thread
Old 11-18-2004, 11:48 PM   #1
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Rep: Reputation: 15
Postgres: can't add constraint

good day to all im badly need a help.
im using this script to add a constraint on my table but i have encountered an error:

\c - dbadmin
SET search_path = public, pg_catalog;

ADD CONSTRAINT "$1" FOREIGN KEY (personality_id) REFERENCES personalities(personality_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
Heres the result and ERROR:
psql:add_const.sql:04: NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
psql:add_const.sql:04: ERROR: $1 referential integrety violation - key referenced from personalities_etc not found in personalities
here's my db schema:

TABLE: personalities
column | type | modifiers
personality_id | Integer | not null default nextval ('public.personalities_personality_id_seq'::text)
personality_nam_last | names_last | not null
personality_nam_first | names_first | not null
personality_nam_middle | names_middle |
personality_nam_suffix | names_suffix |
personality_nam_alias | charcter varying (50) |
birth_date | date | not null
gender | character (1) | not null
db_user | db_users | not null default "currect user"()
timestamp | timestamps | not null default now()
Indexes: personalities_pkey primary key btree (personality_id),
personalities_personality_nam_last_key unique btree (personality_nam_last, personality_nam_first, birth_date),
idx_personalities_db_user btree (db_user),
idx_personalities_timestamp btree ("timestamp")
Check constraints: "personalities_gender" ((gender='M' ::bpchar) OR (gender='F' ::bpchar))
Foreign key constraints: $1 Foreign key
TABLE: personalities_etc
column | type | modifiers
personalities__etc_id | integer | not null default nextval ('public.personalities_etc_personality_etc_id_seq'::text)
personality_id | integer |
civil_status | character (1) | not null
citizenship | character (2) |
spouse_nam_last | names_last |
spouse_nam_first | names_first |
spouse_nam_middle | names_middle |
spouse_nam_suffix | names_suffix |
profession | text | not null
street_address | character varying(50) | not null
address_prov_ipd_id | areas_id |
address_munc_ipd_id | areas_id |
address_brgy_ipd_id | areas_id |
db_user | db_users | not null default "currect user"()
timestamp | timestamps | not null default now()
Indexes: personalities_etc_pkey primary key btree (personality_etc_id),
idx_personalities_etc_db_user btree (db_user),
idx_personalities_etc_timestamp btree ("timestamp")
Check constraints: "personalities_etc_citizenship" ((citizenship='NB' ::bpchar) OR (citizenship='NT' ::bpchar))
"personalities_etc_civil_status" ((civil_status='S' ::bpchar) OR (civil_status='M' ::bpchar) OR (civil_status='W' ::bpchar))
Foreign key constraints: $2 Foreign key (address_prov_ipd_id) REFERECES provinces (province_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 Foreign key (address_munc_ipd_id) REFERECES municipalities (municipality_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 Foreign key (address_brgy_ipd_id) REFERECES barangays (bgy_code) ON UPDATE NO ACTION ON DELETE NO ACTION
hope someone could help me how to put back the CONSTRAINT "$1" from personalities_etc.

thanks in advance..
Old 11-25-2004, 06:24 AM   #2
Senior Member
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,994

Rep: Reputation: 76
You have missed out:
ALTER TABLE personalities_etc
from the start of the ADD instruction.

Also, if personality_id is the primary key, then you can delete the (personality_id) from the instruction, and it will just use the primary key as the default.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Postgres Hiper1 Linux - Newbie 8 04-10-2005 06:43 PM
Easiest way to Add Postgres to existing fedora core 3 installation? gfallon Linux - Newbie 2 03-20-2005 01:15 AM
postgres and mysql varun_33 Linux - Software 1 02-12-2005 08:54 AM
MySQL - UNIQUE values across two columns - non-standard constraint issue benr77 Linux - General 2 11-06-2004 11:48 AM
how do I Add multiple columns mysql in postgres Bheki Linux - Software 1 08-28-2003 03:30 PM > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 03:38 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration