Re: [GENERAL] are foreign keys realized as indexes?
On May 13, 2007, at 17:21 , Lew wrote: Peter Childs wrote: Apart from anything a unique constraint is NOT the same as a unique index, as you need a not null constraint on the column as well. Not true, whichever way 'round you meant it. Technically, the UNIQUE constraint is a logical concept which is physically implemented in PostgreSQL via a unique BTREE index. Since there is only one way to implement a UNIQUE constraint in PostgreSQL, the two concepts are very closely tied. However, say one day PostgreSQL as a unique GiST index implementation. Then there are two potentially two physical implementations for the UNIQUE constraint. For pg unique constraint http://www.postgresql.org/docs/8.1/interactive/ddl- constraints.html#AEN2016 In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, Note here, there is no mention of indexes (a implementation issue): just the logical constraints. unique index http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. Here, they're making the distinction between unique and non-unique (BTREE) indexes: implementation. These are subtle points, but worth distinguishing. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] are foreign keys realized as indexes?
On 09/05/07, Lew [EMAIL PROTECTED] wrote: Felix Kater wrote: I am not bound to indexes, however, wonder if foreign keys itself are non-atomic functionality. I mean: if foreign keys are based on some other lower level functionality like indexes or anything else which I could use as a substitute--in what way ever. Of course, I want to gain the same (referential integrity etc.). If foreign keys are, however, something unique which can't be replaced by any other pg function (I am of course not taking into account things like multiple queries bound together by transactions...) then I have to go though it and implement it into my pg interface (looking at the information_schema: This seems to be quite a bunch of work...). Semantics are not a trivial thing. Foreign keys are a fundamental semantic of the relational model. They do not mean the same thing as an index at all. I find it strange that anyone would resist the notions of primary and foreign keys, when they are the basis of the relational model. Indexes aren't even part of the relational model - they are a hack to enhance performance. Sure they ultimately break down to machine instructions, but that's in a whole different domain of discourse. A data model is built up from primary keys, foreign keys and dependent data. They are fundamental. They /are/ the building blocks of your database. Expressing these molecular concepts in terms of their constituent atoms will not convey the molecular properties; you lose a tremendous amount of information. Just use the syntax that best expresses your structure: PRIMARY KEY and FOREIGN KEY. Apart from anything a unique constraint is NOT the same as a unique index, as you need a not null constraint on the column as well. Peter.
Re: [GENERAL] are foreign keys realized as indexes?
Felix Kater wrote: I am not bound to indexes, however, wonder if foreign keys itself are non-atomic functionality. I mean: if foreign keys are based on some other lower level functionality like indexes or anything else which I could use as a substitute--in what way ever. Of course, I want to gain the same (referential integrity etc.). If foreign keys are, however, something unique which can't be replaced by any other pg function (I am of course not taking into account things like multiple queries bound together by transactions...) then I have to go though it and implement it into my pg interface (looking at the information_schema: This seems to be quite a bunch of work...). Semantics are not a trivial thing. Foreign keys are a fundamental semantic of the relational model. They do not mean the same thing as an index at all. I find it strange that anyone would resist the notions of primary and foreign keys, when they are the basis of the relational model. Indexes aren't even part of the relational model - they are a hack to enhance performance. Sure they ultimately break down to machine instructions, but that's in a whole different domain of discourse. A data model is built up from primary keys, foreign keys and dependent data. They are fundamental. They /are/ the building blocks of your database. Expressing these molecular concepts in terms of their constituent atoms will not convey the molecular properties; you lose a tremendous amount of information. Just use the syntax that best expresses your structure: PRIMARY KEY and FOREIGN KEY. -- Lew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] are foreign keys realized as indexes?
On Tue, 8 May 2007 15:54:08 +0200 Martijn van Oosterhout [EMAIL PROTECTED] wrote: A unique index is not a substitute for a unique constraint, they're exactly the same thing. If you drop your constraint and create a unique index, you're back where you started. You neither added nor removed anything. Yes. For this reason I didn't have to implement *both* 'unique constraints' *and* 'unique indices' in my pg interface. On a certain level foreign keys are just triggers, specially coded to do the work. Yes, you could write your own triggers to do exactly the same thing, but why bother, when someone has written them for you and made nice syntax to use them? My question simply was if I could save coding time... like with 'unique constaints' and 'indeces', see above. However, for what I have learned now, 'foreign keys' can *not* be substituted by indeces, so I have to implement them. Thanks again. Felix ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] are foreign keys realized as indexes?
Felix Kater [EMAIL PROTECTED] writes: On Tue, 8 May 2007 15:54:08 +0200 Martijn van Oosterhout [EMAIL PROTECTED] wrote: A unique index is not a substitute for a unique constraint, they're exactly the same thing. Yes. For this reason I didn't have to implement *both* 'unique constraints' *and* 'unique indices' in my pg interface. If you are trying to get away with a dumbed-down subset of SQL, be prepared for people to refuse to use your tool ;-). You have to support the unique-constraint syntax because the SQL spec says so (and people are used to it), and you have to support the create-index syntax because it gives access to functionality not available through the constraint syntax. Unique indexes on expressions for instance. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] are foreign keys realized as indexes?
Hi, I like to keep my pg interface small: Can I replace foreign keys by using indexes somehow? (This is at least possible for primary key columns which can be replaced by suitable indexes.) Thank You Felix ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] are foreign keys realized as indexes?
On 08/05/2007 12:32, Felix Kater wrote: I like to keep my pg interface small: Can I replace foreign keys by using indexes somehow? (This is at least possible for primary key columns which can be replaced by suitable indexes.) You can do that, but you'll lose the enforcement of referential integrity, which is what foreign keys give you. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] are foreign keys realized as indexes?
On Tue, 08 May 2007 12:48:30 +0100 Raymond O'Donnell [EMAIL PROTECTED] wrote: You can do that, but you'll lose the enforcement of referential integrity, which is what foreign keys give you. If I get you right: There is *no complete* substitute for foreign keys by using *indexes* since I'd loose the referencial integrity (whereas for unique contraints there *is* a full replacement using indexes)? Felix ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] are foreign keys realized as indexes?
Am Dienstag, 8. Mai 2007 13:32 schrieb Felix Kater: I like to keep my pg interface small: Can I replace foreign keys by using indexes somehow? Not while preserving the semantics. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] are foreign keys realized as indexes?
On 08/05/2007 13:14, Felix Kater wrote: There is *no complete* substitute for foreign keys by using *indexes* since I'd loose the referencial integrity (whereas for unique contraints there *is* a full replacement using indexes)? Here's my understandingan index is just that (an index) and no more - it tells PostgreSQL where to look in a table to find a particular row or set of rows. A foreign key, on the other hand, ensures that referential integrity is enforced: it enforces the relationship between rows in a table which refer to rows in another table, depending on how the foreign key was specified in the first place (cf. the ON UPDATE... ON DELETE... etc. clauses). When you have a foreign key, you can put an index on the foreign key column in the child table for performance reasons, but this *isn't* the same as the foreign key. I don't know about the equivalence of unique constraints and indices - others on the list can answer that. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] are foreign keys realized as indexes?
On Tue, 8 May 2007 14:19:12 +0200 Peter Eisentraut [EMAIL PROTECTED] wrote: I like to keep my pg interface small: Can I replace foreign keys by using indexes somehow? Not while preserving the semantics. I am not bound to indexes, however, wonder if foreign keys itself are non-atomic functionality. I mean: if foreign keys are based on some other lower level functionality like indexes or anything else which I could use as a substitute--in what way ever. Of course, I want to gain the same (referential integrity etc.). If foreign keys are, however, something unique which can't be replaced by any other pg function (I am of course not taking into account things like multiple queries bound together by transactions...) then I have to go though it and implement it into my pg interface (looking at the information_schema: This seems to be quite a bunch of work...). Thank You Felix ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] are foreign keys realized as indexes?
On Tue, May 08, 2007 at 02:14:54PM +0200, Felix Kater wrote: If I get you right: There is *no complete* substitute for foreign keys by using *indexes* since I'd loose the referencial integrity (whereas for unique contraints there *is* a full replacement using indexes)? A unique index is not a substitute for a unique constraint, they're exactly the same thing. If you drop your constraint and create a unique index, you're back where you started. You neither added nor removed anything. On a certain level foreign keys are just triggers, specially coded to do the work. Yes, you could write your own triggers to do exactly the same thing, but why bother, when someone has written them for you and made nice syntax to use them? Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] are foreign keys realized as indexes?
Martijn van Oosterhout [EMAIL PROTECTED] writes: On Tue, May 08, 2007 at 02:14:54PM +0200, Felix Kater wrote: There is *no complete* substitute for foreign keys by using *indexes* since I'd loose the referencial integrity (whereas for unique contraints there *is* a full replacement using indexes)? A unique index is not a substitute for a unique constraint, they're exactly the same thing. If you drop your constraint and create a unique index, you're back where you started. You neither added nor removed anything. Well, actually you added or removed a pg_constraint entry associated with the index ... but either way it's the unique index that really does the work of enforcing uniqueness. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
