472,993 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,993 software developers and data experts.

what about uniqueness of inherited primary keys

Hello list,

what about uniqueness of inherited primary keys ?

eg you have :

create table objects (
id int4,
date_created timestamp(0),
primary key (id)
);

create table persons (
firstname varchar(100),
lastname varchar(100)
) inherits (objects);

now ...

insert into objects (id) values (1);
A repetition of this line would cause an unique-constraint error of
objects_pkey.

insert into persons (id, firstname, lastname) values (1, 'Super', 'Man');
insert into persons (id, firstname, lastname) values (1, 'Bat', 'Man');
works though it violates the inherited constraint.

A select * from objects; shows id=1 three times.

delete * from objects; empties both tables.

after ALTER TABLE public.persons ADD CONSTRAINT persons_pkey PRIMARY
KEY(id);
I can still insert at least one record with id=1 in each table.

select count(*) from objects where id=1;
results 2

Shouldn't we expect to have unique entries in a primary key collumn ?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
6 2431
I've been told that not all features of inheritance are implemented. What
this means in practice is that you often have to redeclare constraints on
inherited tables. So in your example you'd need to alter table persons and
add the constraint, but even then I think it might be possible to insert an
objects row and a person row with the same id.
--
Scott Ribe
sc********@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2
On Sun, 2003-12-28 at 20:17, Andreas wrote:
what about uniqueness of inherited primary keys ? .... Shouldn't we expect to have unique entries in a primary key collumn ?


We should, but it doesn't happen. This is a very long-standing defect
which has not yet been solved. To solve it would need an index attached
to multiple tables and the mechanism for that does not yet exist.

The same applies to foreign keys; these too are not inherited.

The solution for primary keys is something like this:

CREATE TABLE tree_index (tboid oid, id varchar(10),primary key
(id),unique (tboid, id));

CREATE TABLE person (id varchar(10) primary key, surname text,foreign
key (tableoid, id) references tree_index(tboid, id) match full on update
cascade on delete cascade);

CREATE TABLE parent (eldest_child varchar(10), primary key (id),foreign
key (tableoid, id) references tree_index(tboid, id) match full on update
cascade on delete cascade) inherits (person);

CREATE TABLE child (father varchar(10) references parent(id), mother
varchar(10) references parent(id), primary key (id), foreign key
(tableoid, id) references tree_index(tboid, id) match full on update
cascade on delete cascade) inherits (person);

alter table parent add constraint "child key" foreign key (eldest_child)
references child (id) on update cascade on delete restrict;

Then you need a trigger on each table to create a record in tree_index
when a new record is added to a table in the hierarchy.

--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For God shall bring every work into judgment,
with every secret thing, whether it be good, or
whether it be evil." Ecclesiastes 12:14
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3
Andreas wrote:
Hello list,

what about uniqueness of inherited primary keys ?

eg you have :

create table objects (
id int4,
date_created timestamp(0),
primary key (id)
);

create table persons (
firstname varchar(100),
lastname varchar(100)
) inherits (objects);
now ...

insert into objects (id) values (1);
A repetition of this line would cause an unique-constraint error of
objects_pkey.

insert into persons (id, firstname, lastname) values (1, 'Super', 'Man');
insert into persons (id, firstname, lastname) values (1, 'Bat', 'Man');
works though it violates the inherited constraint.

A select * from objects; shows id=1 three times.

delete * from objects; empties both tables.

after ALTER TABLE public.persons ADD CONSTRAINT persons_pkey PRIMARY
KEY(id);
I can still insert at least one record with id=1 in each table.

select count(*) from objects where id=1;
results 2

Shouldn't we expect to have unique entries in a primary key collumn ?


Hello Andreas,

for primary keys there is a simple (and at least working for me)
solution as long as you can use the SERIAL type for your primary
key.

CREATE TABLE objects (
id SERIAL PRIMARY KEY,
date_created timestamp(0)
);
CREATE TABLE persons (
id INTEGER PRIMARY KEY,
firstname varchar(100),
lastname varchar(100)
) inherits (objects);

Now the id column gets merged and you should have the desired
behaviour.

If you want objects.id to get referenced by other tables you have
to work around with triggers and an extra table. For persons.id
everything is working fine.

This solution (workaround) is only working as long you don't
insert id-values without updating the corresponding sequence.

HTH

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4
Seastian Böck wrote:
for primary keys there is a simple (and at least working for me)
solution as long as you can use the SERIAL type for your primary
key.
[...]
Now the id column gets merged and you should have the desired
behaviour.

If you want objects.id to get referenced by other tables you have
to work around with triggers and an extra table. For persons.id
everything is working fine.

This solution (workaround) is only working as long you don't
insert id-values without updating the corresponding sequence.

Hello Se(b)astian
-- you left out the 'b' in your e-mail setup ;)

right, your proposal does in a way behave like I wanted. Though the idea
of integrity control by the db-server is still not there for parent
id-colomns. Every user or application could mess up the primary key of
the inherited table. That spoils a bit of the oo-approach, I fear.

It wouldn't be that bad, if the table contents weren't merged in SELECTs.

Probaply one could do some trigger-magic to check the inserted id
against an id-pool in another table.
If one knew anything about triggers that is ... well ... miles to go
before I sleep ...

Thanks
Andreas
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #5
Andreas wrote:
Seastian Böck wrote:
for primary keys there is a simple (and at least working for me)
solution as long as you can use the SERIAL type for your primary
key.
[...]
Now the id column gets merged and you should have the desired
behaviour.

If you want objects.id to get referenced by other tables you have
to work around with triggers and an extra table. For persons.id
everything is working fine.

This solution (workaround) is only working as long you don't
insert id-values without updating the corresponding sequence.


Hello Se(b)astian
-- you left out the 'b' in your e-mail setup ;)

right, your proposal does in a way behave like I wanted. Though the idea
of integrity control by the db-server is still not there for parent
id-colomns. Every user or application could mess up the primary key of
the inherited table. That spoils a bit of the oo-approach, I fear.


I rechecked that and the conclusion is very simple:
it only works reliable if the id is autogenerated by the SERIAL type.

It wouldn't be that bad, if the table contents weren't merged in SELECTs.

Probaply one could do some trigger-magic to check the inserted id
against an id-pool in another table.
If one knew anything about triggers that is ... well ... miles to go
before I sleep ...


For all other situations take a look at Oliver's mail.

Sebastian
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #6
Hi all;

The real problem with using the trigger approach is as follows:

Normally the unique constraint is handled by an index (most efficient way),
but doing so using a trigger is more likely to lose this benefit and give
you some performance problems.

IIRC, the problems with inherited keys are, at present, the fact that
inherited indexes pose some problems. Again, custom triggers would solve
that problem perhaps at a performance cost.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
1
by: Puvendran Selvaratnam | last post by:
Hi, First of all my apologies if you have seen this mail already but I am re-sending as there were some initial problems. This query is related to defining indexes to be unique or not and...
7
by: robertbrown1971 | last post by:
I just inherited a Java application with a fairly complex data model that does not yet have any indexes except those on primary keys. It is still in development and before I get to do any...
1
by: Greg Hulands | last post by:
I have a table called Person that has a primary key personID. Another table called ProUser that inherits from Person. In another table called ProPriceSchedule I have a foreign key constraint to the...
4
by: Fred | last post by:
Hi. What is the usual and what are the possible fields to use for the primary key of an intersecting table of a many-to-many relationship? I would think the typical, most common fields would...
7
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am...
18
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many...
669
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic...
2
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.