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:
add_const.sql
Code:
\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:
Quote:
SET
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
Quote:
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
Quote:
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..