OraFAQ Forum: SQL & PL/SQL » Restricting blank entries

Home » SQL & PL/SQL » SQL & PL/SQL » Restricting blank entries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Restricting blank entries [message #8533]

Thu, 28 August 2003 09:48

Kuldeep
Messages: 21
Registered: June 2002

Junior Member

I have a table that has one field, "ID" as the primary key. ID is CHAR and of lenght 8. The table has 15 other fields. ID has been designated as NOT NULL and PRIMARY KEY. However, I can still insert blanks into this field. So when i write my insert statement [[insert into sale_client (ID) Values (' ');]] 1 row is inserted. The problem is that though oracle allows this to happen, Delphi does not like this blank field. I want to be able to prevent the user from entering a blank in this field, either from the database level [[by modifying the table]] or from the program. Could you help?
Thank you,
Kuldeep

Report message to a moderator

Re: Restricting blank entries [message #8535 is a reply to message #8533]

Thu, 28 August 2003 10:00

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

please check the ddl of the table.
if column ID is defined as primary key, there is no way that oracle can accept a null value.

Report message to a moderator

Re: Restricting blank entries [message #8536 is a reply to message #8535]

Thu, 28 August 2003 10:17

Kuldeep
Messages: 21
Registered: June 2002

Junior Member

Mahesh,
Thank you for your reply. Well, the entry is not a null. It is a blank. like when you insert [[insert into table (ID) values (' '); you hit enter 4 or five times and a row is created. this is different from NULL.

Try it out:
Create table XXX
(ID CHAR(8) NOT NULL,
NAME VARCHAR2(12),
PRIMARY KEY (ID));

Then do:

insert into XXX (ID,NAME) VALUES (' ', 'MAHESH');

You will be able to insert. I want to prevent this.

Thanks,
Kuldeep

Report message to a moderator

It's a primary key also ? [message #8537 is a reply to message #8536]

Thu, 28 August 2003 10:26

aix_tom
Messages: 4
Registered: August 2003

Junior Member

You could simly leave ONE entry with a blank in there and nobody will be able to insert a second one. ;-)

Id is an Char field, a blank is an char, so oracle will insert it. You could only catch in on the application level, or maybe with an "before insert" trigger that would set the value to NULL if it is an blank, so Oracle would block it.

Report message to a moderator

Re: It's a primary key also ? [message #8538 is a reply to message #8537]

Thu, 28 August 2003 10:35

Kuldeep
Messages: 21
Registered: June 2002

Junior Member

You are right it is a primary key too. THe problem with leaving a blank key in there is that this table is loaded into a Borland Delphi database every week through a batch job and also into a sql server database on a nightly basis. The other two databases do not like the blank to be there.

Well, I would have hoped that since this is a critical table to our application, the business rules should have been strictly enforced. But the before insert trigger should take care of this. You are right there. I wish there was some kind of simple contraint that would restrict this.

Thank you for your reply.

-Kuldeep

Report message to a moderator

Re: It's a primary key also ? [message #8539 is a reply to message #8538]

Thu, 28 August 2003 10:53

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

-- try something like this
-- this will restrict the first character from being ' '
--
"afiedt.buf" 6 lines, 123 characters

  1  create table test
  2     (id char(8) primary key constraint check_blank check (substr(id,1,1)!=' '),
  3      name varchar(10)
  4*  )
  5  /

Table created.

dbadmin@mutation_mutation > insert into test values (' ','ma');
insert into test values (' ','ma')
*
ERROR at line 1:
ORA-02290: check constraint (DBADMIN.CHECK_BLANK) violated

Report message to a moderator

Re: It's a primary key also ? [message #8547 is a reply to message #8539]

Thu, 28 August 2003 15:44

Kuldeep Singh
Messages: 4
Registered: February 2002

Junior Member

Mahesh,
Thank you for answering my question. I think we will use the option that you suggested. I will also write BI triggers for other similar issues that we have. Kuldeep

Report message to a moderator

Previous Topic:	Trigger for Insert and Update
Next Topic:	exception in trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 11:34:21 CDT 2024