LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
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!

Notices


Reply
  Search this Thread
Old 11-18-2004, 11:48 PM   #1
vickr1z
Member
 
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:

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..
 
Old 11-25-2004, 06:24 AM   #2
rjlee
Senior Member
 
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,990

Rep: Reputation: 69
You have missed out:
Code:
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.
 
  


Reply


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


All times are GMT -5. The time now is 08:21 PM.

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