Обсуждение: Foreign key from another database
Hi, Can I have a reference (with cascading), to another database? Let me try to explain the situation a little better ;-) I have a "main" database, clients. Then I have another database. Let's call that program1. In program1 I have a table pTable and in clients I have a table namned Client. Like this: \c clients CREATE TABLE Client ( CustId Serial PRIMARY KEY, .... ); \c program1 CREATE TABLE pTable ( pId SERIAL PRIMARY KEY, CustId INTEGER, .... ); I would like the data to be dropped in pTable automatically, when the client is deleted.... / Antti
Antti Ijäs wrote: > Can I have a reference (with cascading), to another database? Let me > try to explain the situation a little better ;-) > You can write a trigger that uses functions from contrib/dblink to execute a command in another database. However, without two-phase commit support I don't think you can be sure the entire operation is atomic. Joe
You might want to look into using multiple schemas in a single database rather than multiple databases. If you switch to the above, then you can do what you're looking for. Postgresql has limited cross database abilities (contrib/dblink), but not cross database foreign keys. On Thu, 2003-04-03 at 04:16, Antti Ijäs wrote: > Hi, > > Can I have a reference (with cascading), to another database? Let me > try to explain the situation a little better ;-) > > I have a "main" database, clients. Then I have another database. Let's > call that program1. In program1 I have a table pTable and in clients I > have a table namned Client. Like this: > > \c clients > CREATE TABLE Client ( > CustId Serial PRIMARY KEY, > .... > ); > > \c program1 > CREATE TABLE pTable ( > pId SERIAL PRIMARY KEY, > CustId INTEGER, > .... > ); > > I would like the data to be dropped in pTable automatically, when the > client is deleted.... > > / Antti > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Use trigger on Client after delete, and dblink from contrib. C. Antti Ijäs wrote, On 4/3/2003 11:16 AM: > Hi, > > Can I have a reference (with cascading), to another database? Let me > try to explain the situation a little better ;-) > > I have a "main" database, clients. Then I have another database. Let's > call that program1. In program1 I have a table pTable and in clients I > have a table namned Client. Like this: > > \c clients > CREATE TABLE Client ( > CustId Serial PRIMARY KEY, > ..... > ); > > \c program1 > CREATE TABLE pTable ( > pId SERIAL PRIMARY KEY, > CustId INTEGER, > ..... > ); > > I would like the data to be dropped in pTable automatically, when the > client is deleted.... > > / Antti