Adding null value in one of the primary key field

Author: lalitpct@gmail.com (lalitpct)

We have 3 fields which are primary key and one of the filed is float type , I want to have null value in that field but uniface gives an error .Insert from sybase allows null value. I set syntax for that field as LEN[0-0) but still it doens work. after making syntax LEN(0-0) when i tried I am able to insert null , but deletion doesnt work properly (gives error -300) and also update

4 Comments

  1. HI Uli, Thansk for the response , right in normal scenario we dont have primary key as null for that we define them as not null in database. However if we dont specify then it does take null value. Actully in uniface the key starts behaving weird in such scenario For example .. I inserted null and was able to insert and after that when I try to insert 0 it gives below error. same error is there when i insert null first and then try to insert 0 (for other values it works fine and scenario where we dont insert null everything is ok) (_validatekey) VLDK:9 findkey $entname, $curkey (_validatekey) VLDK:10 selectcase $status (_validatekey) VLDK:15 case 1 (_validatekey) VLDK:16 if ( !$foreign) (_validatekey) VLDK:17 return (-1) End ent TOUC_FLAG, occ 3, status -1 Object size=0000059 msg/0147@smart@usa.msg Sys ent TOUC_FLAG, occ 3, status -35 i understand this might be the expected behavious as having null usually dont make sense as primary key. To workaround ,..i was thinking that avoid validating keys for values (0,null) and use sql statement, is that recommended one


    Author: lalitpct (lalitpct@gmail.com)
  2. Hi lalit, once I thought, in SQL databases all primary keys have to be "NOT NULL" by definition, new for me that sybase allows NULLs. Looks like the uniface database driver can not handle this speciality properly (perhaps it's just that INSERT put some default instead of NULL). I checked the helpfile for SYBASE and NULL but the staterments were not very clear on that matter Perhaps your company can ask their local CPWR contact for explanation, don't raise a call IMHO this is a waste of time Have you checked the messageframe and $dberror/$dberrortext ?


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. Hi Lalit Is this a legacy system, and so it's too late to change the primary key? To fully understand the situation it would be helpful to see the full DDL statements for the table, i.e. it sounds like the there is no proper unique constraint on this primary key. The Transact-SQLmanual is fully consistent with the first rule of relational theory - quote: "The primary key is a column or combination of columns with values that uniquely identify a row. The value cannot be null and must have a unique index. A table with a primary key is eligible for joins with foreign keys in other tables." So relational DBMS systems support relational databases, but don't force them on to you. I think it's even possible to change the default setting for whether a column is NOT NULL or not. In any case is sounds like the DBMS schema is inconsistent with the Uniface application model. This isn't the best forum to talk about the dangers of nulls in keys. Chris Date covered that ad-nauseum in the 90s. I just think it's best to have consistency between the two definitions. If that float field cannot avoid a null, then the convential wisdom is to find another primary key, and perhaps make these 3 columns a candidate key.


    Author: Eddy Knochs (eknochs@ozemail.com.au)
  4. I have to admit I have been sitting on the comment, "It sounds like your data file is wrong." It'd be an odd design if the primary key REALLY required a nullable float. It's possible, even if it is a legacy system that you could find a/the real unique identifier, and create another index on that, then tell uniface that's the P.K.


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)