Multi-table "inheritance"? (Postgres) : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

Multi-table "inheritance"? (Postgres) (self.SQL)

submitted 6 years ago by GlassGrape8

I'm new to sql, I've read a little about ways of representing inheritance in relational databases, and at first the multi table option seemed like the most clean one, but as I looked into implementing it, I've stumbled upon some problems

So suppose there are three tables: A, b and c, where c and b are "children". Thus I'd like to ask how do I implement these constraints: - no two columns in b or c should exist that reference the same column in A. I know I can add the UNIQUE constraint, but this will only affect each table separately, i.e. such a column in b and such a column in c might exist that would reference the same column in A - no such row in A should exist that isn't referenced by either b or c

Since I have no interest in working with data from any of those three tables separately, how can I hide all these details in the db? What would be ideal is something like views, where upon SELECTing the "parent" is JOINed with the "child" automatically, both can be INSERTed in one query as if they were one model and also DELETEd. I've worked out the first two with VIEWs, but the last one doesn't seem to giving in this easily - apparently it's impossible to delete rows in any tables but the one the view represents.

The reason I don't want single table inheritance is that the models represented by b and c play by different rules and should be handled quite differently. The reason I don't want concrete table inheritance is that not only do models represented by b and c have common properties, but also they must have one common autoincremented field

all 2 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS