X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 861763A4348 for ; Thu, 6 Jan 2005 00:20:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86290-08 for ; Thu, 6 Jan 2005 00:20:46 +0000 (GMT) Received: from laptop.kieser.net (kieser2.demon.co.uk [80.176.229.121]) by svr1.postgresql.org (Postfix) with ESMTP id 034093A4335 for ; Thu, 6 Jan 2005 00:20:36 +0000 (GMT) Received: from [127.0.0.1] (laptop.kieser2.demon.co.uk [127.0.0.1]) by laptop.kieser.net (Postfix) with ESMTP id C97CF18328 for ; Thu, 6 Jan 2005 00:20:44 +0000 (GMT) Message-ID: <41DC845C.1020106@kieser.net> Date: Thu, 06 Jan 2005 00:20:44 +0000 From: Bradley Kieser User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en, en-us MIME-Version: 1.0 To: PgSQL ADMIN Subject: Nullable columns and views Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.331 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/29 X-Sequence-Number: 15888 I have created a schema with views on top of tables. We use JDBC to connect to the schema and we use JBuilderX to develop our app. Every table as an "id" column of type serial. I.e. It is not null with default nextval(...). However, it seems that when a column from a table is used in a view, the not null part (as well as the default values) seems to be dropped. Serials become integers. I can use "alter table" to restore default values on the views, but "alter table" to make a view column not null results in an error saying that the viewname is not a table! I.e. I don't seem to be able to make the column not null. I think that this is throwing JBuilder's resolver into chaos because it doesn't use the column that I have specified as ROWID (column "id" for each view) to resolve update rows back to the view, it tries to use every single column, a sure sign that it thinks that the column I have told it is a rowid isn't up to the job. I am pretty certain that it's because it thinks that column can be null. Unfortunately I have to use views and cannot use tables because of security in the app. The views have rules set on them as well as permissions to specific users. So the answer isn't to open up the database and use the underlying tables instead. That would open a security hole in the app. Moreover, I use views to do complex processing in PG (e.g. multi-table joins or rules and triggers on updates to ensure data integrity and implement business logic). JBuilder, being a RAD is great at building apps from simple datasets (i.e. single views and tables), but it quickly starts to wilt under complex parametised queries! So for these reasons I need to find a way to tell JBuilder (through JDBC) that the ID columns are not nullable. But how? Can someone please help me? Thanks, Brad