I have a table called Person that has a primary key personID. Another
table called ProUser that inherits from Person. In another table called
ProPriceSchedul e I have a foreign key constraint to the ProUser table,
like so: CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES
ProUser(personI D).
The problem I am having is this: ERROR: UNIQUE constraint matching
given keys for referenced table "prouser" not found
Here are the tables for reference:
CREATE SEQUENCE pro_price_sched ule_id;
CREATE TABLE ProPriceSchedul e
(
scheduleID int8 PRIMARY KEY DEFAULT NEXTVAL('pro_pr ice_schedule_id '),
personID int8 NOT NULL,
title varchar (32) NOT NULL CHECK (title <> ''),
CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personI D)
);
CREATE SEQUENCE user_id;
CREATE TABLE Person
(
personID int8 PRIMARY KEY DEFAULT NEXTVAL('user_i d'),
firstName varchar (32) NOT NULL CHECK (firstName <> ''),
lastName varchar (32) NOT NULL CHECK (lastName <> ''),
address1 varchar (64) NOT NULL CHECK (address1 <> ''),
address2 varchar (64) ,
suburb varchar (32) NOT NULL,
stateID int2 NOT NULL,
postcode varchar (8) NOT NULL,
phone varchar (16) ,
mobile varchar (16) ,
email varchar (128) NOT NULL UNIQUE,
password varchar (16) NOT NULL,
CONSTRAINT state FOREIGN KEY (stateID) REFERENCES State(stateID)
);
CREATE TABLE ProUser
(
businessName varchar (64) NOT NULL CHECK (businessName <> ''),
abn varchar (16) NOT NULL CHECK (abn <> ''),
homepageHTML varchar (4096) ,
creditLimit decimal (10, 2) ,
enforceLimit bool NOT NULL,
fax varchar (16)
) INHERITS (Person); 1 2175
On Tue, 4 Nov 2003, Greg Hulands wrote: I have a table called Person that has a primary key personID. Another table called ProUser that inherits from Person. In another table called ProPriceSchedul e I have a foreign key constraint to the ProUser table, like so: CONSTRAINT proUser FOREIGN KEY (personID) REFERENCES ProUser(personI D).
The problem I am having is this: ERROR: UNIQUE constraint matching given keys for referenced table "prouser" not found
That's because there isn't one. Primary keys, unique constraints and
foreign key constraints don't meaningfully inherit currently. So, for now
ProUser has no unique constraint on the personID field unless you define
one in ProUser (and said constraint does not prevent a personID in Person
from being duplicated in ProUser).
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dcp |
last post by:
I just installed the 4.1.0-alpha-max-nt version of MySql and have just
started playing around with it.
My first test was to try to create a couple of tables, one with a
foreign key constraint. I can't seem to figure out how to drop the
foreign key constraint, I read through the docs and it says I need to
do a 'show create table <table_name>' to get the internally generated
foreign key id, but this does not show up when I do the show...
|
by: geoff |
last post by:
The table creation script(at the end of this post) works fine on
4.0.1-alpha-win, but the foreign key constraints fail on 4.0.15-win. I
am starting the server with the same command for both versions:
mysqld-max-nt --console --transaction-isolation=SERIALIZABLE
In 4.0.15-win I can extract the following error after I run the table
creation script:
ERROR 1005: Can't create table '.\ibdata\#sql-a14_3.frm'
|
by: Bodza Bodza |
last post by:
I'm having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it's not supposed
to be optional, it's very definition is it's a necessary link to the
parent table and part of the definition. If it's optional it shouldn't
be part of the definition of a table and should be in a linking table
instead.
Comments?
|
by: Robert Brown |
last post by:
Let's say I have a type hierarchy: (just an example)
the general entity customer:
CREATE TABLE customer(customer_id int, customer_name varchar(250),
customer_type int)
three specific customer subtypes: 1 - business, 2 - home, 3 -
university
|
by: Scott Ribe |
last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if
I'm missing something. Two tables, A & B have foreign key relations to each
other. A 3rd table C, inherits from A. A stored procedure updates a row in
C, adds a row each in B & C. I get an integrity violation. All the foreign
keys are deferrable, and the stored procedure is called from within a
transaction with constraints deferred. (And the foreign keys do refer to...
| |
by: Brendan Jurd |
last post by:
Hi all,
I read on the manual page for Inheritance that:
"A limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. Thus, in the above example,
specifying that another table's column REFERENCES cities(name) would
allow the other table to contain city names but not capital names. This
deficiency will probably be fixed...
|
by: Shawn Harrison |
last post by:
Greetings,
I'm using pg 7.3.5 and playing with table inheritance, and I've run into the
fact that foreign keys cannot be defined on inherited attributes. (As much
is stated in the documentation, but it didn't sink in until I ran into the
fact.)
The documents say this will probably be fixed in a future release. My
question is, are there any definite plans in that direction at this point?
|
by: Alex Satrapa |
last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg:
CREATE TABLE foo (
id SERIAL PRIMARY KEY
);
CREATE TABLE bar (
attribute integer NOT NULL
) INHERITS (foo);
|
by: Eva Hovelsrød |
last post by:
Hi, I just realized that foreign keys to an inherited table don't work when
the record was inserted into a child table. That is postgresql-7.3.2-3.
Does anyone know whether anyone is working on that area or if there are any
plans to?
Or is there a simple workaround, like in the foreign key definition to say
that a column should reference the parent table OR the child table (i.e.,
providing a list of tables to look for references in)?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |