Re: [SQL] referencial conlumn contraints and inheritance
Stuart, > I am not sure where to post this. This may be more of a wishlist > item > than a usage question. I am new to postgresql 7.1. Table > inheritance > in postgres seems wonderfull. Is there the capability now or any > plans to extend support for inherited tables in column constraints. > One > example is a travel db where several tables are defined as: There have been, as I understand it, several improvements made to inheritance and constraints in 7.2 (released soon, really!). However, you're potentially in for a world of painful workarounds here. REFERENCES constraints are part of a Relational DBMS system; Inheritance is an OODB concept. The two do not, IMHO, mix easily. Further, current implementations of table inheritance are largely proprietary, meaning that your application becomes impossible to part across platforms. If I were designing your database, I would forget about inheritance, and build it completely relationally, either with an tree-structured Areas table keyed to area subtypes, or with heirarchical tables of country --> region --> province --> city etc. Which structure you use depends largely on the rest of the application. However, keep in mind that a lot of people (the Postgres core team included) do not agree with me about inheritance and its limitations. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] referencial conlumn contraints and inheritance
Folks, I am not sure where to post this. This may be more of a wishlist item than a usage question. I am new to postgresql 7.1. Table inheritance in postgres seems wonderfull. Is there the capability now or any plans to extend support for inherited tables in column constraints. One example is a travel db where several tables are defined as: create table areas ( area_id char(10) primary key, name varchar(40), . description varchar(120) ); create table countries ( . . ) inherits (areas); create table states ( . country_id char(10) references countries ..., . . ) inherits (areas); create table cities ( . country_id char(10) references countries ..., . .) inherits (areas); Then a destination table set up with a field referencing areas. Currently postgres appears not to support reference contraints checking through inherited tables. Nor does it appear to support subselects in check constraints. It would be nice to allow reference checking through the inherited tables so destination.area_id would be valid for any country, state, or city area_id entered. create table destination ( . area_id char(10) references areas ..., . .); Thanks, Stuart ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] referencial conlumn contraints and inheritance
On Sun, 14 Oct 2001, Stuart wrote: > Then a destination table set up with a field referencing areas. > Currently postgres appears not to support reference contraints checking > through inherited tables. Nor does it appear to support subselects in check > constraints. It would be nice to allow reference checking through the > inherited tables so destination.area_id would be valid for any country, > state, or city area_id entered. This isn't currently supported, and probably isn't going to be supported for a version or two at least. There are some problems in the current inheritance scheme (for example, IIRC your primary key is also not inherited, and even if you define one on the child it won't stop you from having duplicates between the tables) and this is one of them. The work to do this isn't so huge, but there are some issues about inheriting the triggers that would need to be sat down and thought about. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
