[SQL] Mechanics of Update:Cascade
Hi, could someone please explain to me the mechanics of an UpDate:Cascade? Delete:Cascade I fully understand but not Update. I have 2 tables A and B. On B I have created a foreign key on user_id in both B and A for Update and Delete cascade. If I delete from A it deletes all from B. If I update A what happens in B? many thanks ---(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
Re: [SQL] [NOVICE] 'select nextval('seq_name');' in a function ?
On Sat, 24 Jan 2004, Pragati Kenkare wrote:
> I am new to postgresql. Using PostgreSQL 7.3.2, I did the following.
>
> testdb#CREATE SEQUENCE principal_id increment 1 start 1000 cache 5;
>
> testdb#CREATE TABLE principal (principal_id int not null, name text, constraint
> pk_principal primary key(principal_id));
>
> testdb#CREATE FUNCTION getnext_principal_id(int) returns int as 'select
> nextval('principal_id');' language 'SQL';
You need to double the single quotes around principal_id, and I'm not sure
which version switched to int8 sequences, and do you really want to be
passing in an unused int?
CREATE FUNCTION getnext_principal_id() returns int as 'select
CAST(nextval(''principal_id'') AS int4);' language 'SQL';
However, how are you planning to use this? Perhaps making principal_id a
serial would be better for you.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Mechanics of Update:Cascade
On Sat, 31 Jan 2004, beyaRecords - The home Urban music wrote: > Hi, > could someone please explain to me the mechanics of an UpDate:Cascade? > Delete:Cascade I fully understand but not Update. I have 2 tables A and > B. On B I have created a foreign key on user_id in both B and A for > Update and Delete cascade. If I delete from A it deletes all from B. If > I update A what happens in B? Basically it means that if you update the referenced key in A, the associated referencing values in B changes as well so as to attempt to keep the associations the same. Here's an example: sszabo=# create table a(a int primary key, b int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE sszabo=# create table b(a_key int references a on update cascade, b int); CREATE TABLE sszabo=# insert into a values (3, 4); INSERT 535937 1 sszabo=# insert into a values (4, 5); INSERT 535938 1 sszabo=# insert into b values (3,104); INSERT 535939 1 sszabo=# insert into b values (4,105); INSERT 535940 1 sszabo=# select * from a; a | b ---+--- 3 | 4 4 | 5 (2 rows) sszabo=# select * from b; a_key | b ---+- 3 | 104 4 | 105 (2 rows) sszabo=# update a set a=a+100; UPDATE 2 sszabo=# select * from a; a | b -+--- 103 | 4 104 | 5 (2 rows) sszabo=# select * from b; a_key | b ---+- 103 | 104 104 | 105 (2 rows) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Mechanics of Update:Cascade
On Saturday 31 January 2004 10:50, beyaRecords - The home Urban music wrote: > Hi, > could someone please explain to me the mechanics of an UpDate:Cascade? > Delete:Cascade I fully understand but not Update. I have 2 tables A and > B. On B I have created a foreign key on user_id in both B and A for > Update and Delete cascade. If I delete from A it deletes all from B. If > I update A what happens in B? The column should be changed there too. For example, if we have B.user_id => A.user_id and change A.user_id from 100 to 200 then the same should happen in B too. -- Richard Huxton Archonet Ltd ---(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
[SQL] 'select nextval('seq_name');' in a function ?
Hi,
I am new to postgresql. Using PostgreSQL 7.3.2, I did the following.
testdb#CREATE SEQUENCE principal_id increment 1 start 1000 cache 5;
testdb#CREATE TABLE principal (principal_id int not null, name text, constraint
pk_principal primary key(principal_id));
testdb#CREATE FUNCTION getnext_principal_id(int) returns int as 'select
nextval('principal_id');' language 'SQL';
This gives ->
ERROR: parser: parse error at or near "principal_id" at character ...
Please help. Basically, I want to write equivalent of the following Oracle procedure:
create or replace procedure getnext_principal_id (retVal out
principal.principal_id%TYPE ) as
begin
select principal_id.nextval into retValue from dual;
end;
Thanks much,
Pragati
--
___
Get your free email from http://www.iname.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Sometimes referential integrity seems not to work
In a database I am working, I sometimes have to delete all the records in some tables. According to the referential integrity defined in the creation of the tables, postmaster should not delete the records, but it does. I have used the following commands: "delete from table_1" and "truncate table_1". When deleting records individually, referential integrity works (postmaster does not allows deletion of records which are referred by another table). Is there some difference between deleting records individually or at once? Is there any possibility of the user who does this deletion (postgres, normal users) affects the behaviour of referential integrity in the tables? That is, can the postgres user delete records despite referential integrity? Thanks. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] An order by question
All, I need a little help on a sorting problem. Imagine a table, call it records, that has fields: lastName firstName term I want to sort the records by last name, then first name, and finally by term. This almost does what I want: select * from records order by lastName, firstName, term; However, the possible values for term are: 2002F 2003S 2003X 2003F Where F is for fall, S for spring, and X for summer session. Thus, a straight alphabetical sort doesn't give me what I want. If the year is the same, then I want a sort with S, then X, then F for identical year. Any suggestions? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Mechanics of UpDate:Cascade
Hi, could someone please explain to me the mechanics of an UpDate:Cascade? Delete:Cascade I fully understand but not Update. I have 2 tables A and B. On B I have created a foreign key on user_id in both B and A for Update and Delete cascade. If I delete from A it deletes all from B. If I update A what happens in B? regards Andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Sometimes referential integrity seems not to work
On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > In a database I am working, I sometimes have to delete all the records in > some tables. According to the referential integrity defined in the creation > of the tables, postmaster should not delete the records, but it does. I have > used the following commands: "delete from table_1" and "truncate table_1". > When deleting records individually, referential integrity works (postmaster > does not allows deletion of records which are referred by another table). > Is there some difference between deleting records individually or at once? > Is there any possibility of the user who does this deletion (postgres, normal > users) affects the behaviour of referential integrity in the tables? That is, > can the postgres user delete records despite referential integrity? It shouldn't. Can you give your version information and a complete standalone example? ---(end of broadcast)--- TIP 3: 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: [SQL] Sometimes referential integrity seems not to work
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: >> In a database I am working, I sometimes have to delete all the records in >> some tables. According to the referential integrity defined in the creation >> of the tables, postmaster should not delete the records, but it does. I have >> used the following commands: "delete from table_1" and "truncate table_1". >> ... >> can the postgres user delete records despite referential integrity? > It shouldn't. Can you give your version information and a complete > standalone example? I think the first PG release or two that had TRUNCATE TABLE would allow you to apply it despite the existence of foreign-key constraints on the table. Recent releases won't though. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Sometimes referential integrity seems not to work
On Sat, 31 Jan 2004, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> In a database I am working, I sometimes have to delete all the records in > >> some tables. According to the referential integrity defined in the creation > >> of the tables, postmaster should not delete the records, but it does. I have > >> used the following commands: "delete from table_1" and "truncate table_1". > >> ... > >> can the postgres user delete records despite referential integrity? > > I think the first PG release or two that had TRUNCATE TABLE would allow > you to apply it despite the existence of foreign-key constraints on the > table. Recent releases won't though. Yeah, truncate didn't worry me much, but the implication that delete from table_1; worked did. ---(end of broadcast)--- TIP 3: 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
