Check constraint violated

3 views
Skip to first unread message

Me

unread,
Jun 13, 2003, 12:03:43 PM6/13/03
to
-- The main table contains no foreign keys
CREATE TABLE LICAGENT
(
NAME VARCHAR2(20) NOT NULL,
AGE NUMBER (2) NOT NULL ,
SEX CHAR (1) NOT NULL,
ADDRESS VARCHAR2(50) NOT NULL,
LICENCE_NO NUMBER(10) NOT NULL UNIQUE,
AGENT_CODE NUMBER(10) NOT NULL,
PRIMARY KEY (AGENT_CODE),
CHECK (AGE > 17 AND AGE<65),
CHECK (LICENCE_NO >0 AND AGENT_CODE>0),
CHECK (SEX IN ('M','F'))
);

CREATE TABLE POLICY
(
POLICY_NAME VARCHAR2(20) NOT NULL,
POLICY_NO VARCHAR2(10) NOT NULL UNIQUE,
FORM_NO VARCHAR2(10) NOT NULL UNIQUE,
CREATION_DATE DATE NOT NULL,
PRIMARY KEY (POLICY_NO)
);

-- Proposers data
-- Prop_no stands for proposal number
CREATE TABLE PROPOSER
(
PROP_NO VARCHAR2(10) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
SHORT_NAME VARCHAR2(10),
AGE NUMBER(2) NOT NULL,
SEX CHAR(1) NOT NULL,
ADDRESS VARCHAR2(50) NOT NULL,
NATIONALITY VARCHAR(20) NOT NULL,
RES_IN_INDIA CHAR (3) NOT NULL,
FATHER_NAME VARCHAR (20) NOT NULL,
UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE,
POLICY_NO VARCHAR2 (10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
PRIMARY KEY (PROP_NO),
CHECK (AGE >0 AND AGE<65),
CHECK (SEX IN ('M','F') ),
CHECK (RES_IN_INDIA IN ('yes','no'))
);

-- Nominee's data

CREATE TABLE NOMINEE
(
NAME VARCHAR2(20) NOT NULL,
AGE NUMBER(2) NOT NULL,
SEX CHAR(1) NOT NULL,
RELATION CHAR(15) NOT NULL,
PRESENT_OCCUPATION CHAR(20) NOT NULL,
UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE ,
POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE ,
PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE ,
CHECK (AGE>0 AND AGE<65),
CHECK (SEX IN ('M','F'))
);

-- Nominee has apointee if age of nominee is less than 18

CREATE TABLE APOINTEE
(
NAME VARCHAR2(20) NOT NULL,
AGE NUMBER(2) NOT NULL,
SEX CHAR(1) NOT NULL,
RELATION CHAR(15) NOT NULL,
PRESENT_OCCUPATION CHAR(20) NOT NULL,
UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE ,
POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE,
CHECK (AGE>18 AND AGE<65),
CHECK (SEX IN ('M','F'))
);

-- Propors Policy data
CREATE TABLE POLICY_DATA
(
AGE_PROOF VARCHAR2(20) NOT NULL,
SUM_ASSURED NUMBER(10) NOT NULL,
AMOUNT_DEPOSITED NUMBER(10) NOT NULL,
TABLE_NO NUMBER (4) NOT NULL,
TERM NUMBER (2) NOT NULL,
BOC_NO NUMBER(10),
BOC_DATE DATE,
PROP_DATE DATE NOT NULL,
PAN VARCHAR2(16) NOT NULL UNIQUE,
UAGENT NUMBER (10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE,
POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE,
CHECK (AMOUNT_DEPOSITED >0 AND AMOUNT_DEPOSITED<1000000),
CHECK (SUM_ASSURED >0 AND SUM_ASSURED<1000000),
CHECK (TERM IN (5,10)),
CHECK (TABLE_NO >0)
);

-- Proposers health data

CREATE TABLE PROP_HEALTH
(
HEIGHT NUMBER (3) NOT NULL,
WEIGHT NUMBER(3) NOT NULL,
PRES_HEALTH CHAR(4) NOT NULL,
ADMIT_HOSP CHAR(3) NOT NULL,
PHY_DEFORMITY CHAR (3) NOT NULL,
UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE ,
POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE ,
PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE ,
CHECK (WEIGHT>0 AND HEIGHT>0),
CHECK (PRES_HEALTH IN ('good','bad')),
CHECK (ADMIT_HOSP IN ('yes','no'))
);

-- Additional data for female proposors

CREATE TABLE FEMALE_PROP_DATA
(
MARRIED CHAR (3) NOT NULL,
PREG_NOW CHAR (3) NOT NULL,
MISCARRIAGE CHAR(3) NOT NULL,
UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE,
POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE,
CHECK (MARRIED IN ('yes','no')),
CHECK (PREG_NOW IN ('yes','no')),
CHECK (MISCARRIAGE IN ('yes','no'))
);
_________________________________________
insert into licagent values ('Harish Patel',0,'a','Calcutta',0,0);

If you do this you get:
ORA-02290: check constraint (SCOTT.SYS_C005196) violated

1) Is there a way of getting an error message as to which check constraint was
violated?

2) How can I put a check constraint that allows entering data into apointee only
if the nominee's age is lesser than 18?

In a similar way entering data into FEMALE_PROP_DATA only if the proposer is a
female etc. etc. (one example will do)

3) http://www.licindia.com/lichome2/300(bn).doc
If you have the time could you tell me:
Is there something wrong with the relations? (This is for a college
project) any suggestions are welcome.


Thanks a lot.
--
Just trying my fate here :)
Send mail to:
some...@despammed.com
In OE press Crl-F3 (view source) to see post properly

Scott Mattes

unread,
Jun 13, 2003, 12:16:37 PM6/13/03
to
Have you looked in all_constraints to find out what is wrong?

It is a GOOD practice to specify the fields list on inserts.


"Me" <some...@despammed.com> wrote in message
news:64tjevsk29678h9vt...@4ax.com...

Me

unread,
Jun 13, 2003, 12:20:31 PM6/13/03
to
On Fri, 13 Jun 2003 16:16:37 GMT, Scott Mattes wrote:

>Have you looked in all_constraints to find out what is wrong?

I did not understand :(

>It is a GOOD practice to specify the fields list on inserts.

Ok.I'll follow it.

But is there a way of making oracle generate a different kind of, more
informative error message?

Thanks.

Sybrand Bakker

unread,
Jun 13, 2003, 12:42:37 PM6/13/03
to


That would start with *naming* the constraints appropiately, wouldn't
it. Just open your manual for the first time, and reading 'constraint
clause' in the 'sql reference manual'.
We are not going to do your work for you for free


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

Daniel Morgan

unread,
Jun 13, 2003, 2:18:56 PM6/13/03
to
Me wrote:

Never build constraints without explicit naming. For example:

pk_table_name - primary key

fk_table_table_field_name - foreign key
fkd_ table_table_field_name - deferrable foreign key

cc_table_column_condition - check constraint

etc.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)


Mike Sherrill

unread,
Jun 13, 2003, 4:32:16 PM6/13/03
to
On Fri, 13 Jun 2003 21:50:31 +0530, Me <some...@despammed.com> wrote:

>But is there a way of making oracle generate a different kind of, more
>informative error message?

Name the constraints. I'm pretty sure that's Best Practice for all
SQL environments.

--
Mike Sherrill
Information Management Systems

Me

unread,
Jun 14, 2003, 3:20:48 AM6/14/03
to
On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
>2) How can I put a check constraint that allows entering data into apointee only
>if the nominee's age is lesser than 18?

Could someone help me with this constraint.

Thanks.

andrewst

unread,
Jun 14, 2003, 7:33:48 AM6/14/03
to

Originally posted by Me
> On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
> >2) How can I put a check constraint that allows entering data
> into apointee only
> >if the nominee's age is lesser than 18?
>
> Could someone help me with this constraint.
>
> Thanks.
ALTER TABLE appointee
ADD CONSTRAINT chk_nominee_age CHECK (age < 18);

--
Posted via http://dbforums.com

Sybrand Bakker

unread,
Jun 14, 2003, 7:38:25 AM6/14/03
to


Trying to avoid to read the manual are we?

decode(sign(<age_column> - 18), 1, false, 0, false, true)

Me

unread,
Jun 14, 2003, 8:51:28 AM6/14/03
to
On Sat, 14 Jun 2003 11:33:48 +0000, andrewst wrote:

>Originally posted by Me
>> On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
>> >2) How can I put a check constraint that allows entering data
>> into apointee only
>> >if the nominee's age is lesser than 18?
>>
>> Could someone help me with this constraint.

>ALTER TABLE apointee


>ADD CONSTRAINT chk_nominee_age CHECK (age < 18);

No, that would not work. What I mean is this:
There are two separate relations by name nominee and apointee; a policy holder
will always have a nominee, if the nominee is a minor only then he will have an
apointee whose age _will_ have to be greater than 18.

At the same time the proposer's nominee's age must be lesser than 18 (minor)

Thanks.

Me

unread,
Jun 14, 2003, 8:51:30 AM6/14/03
to
On Sat, 14 Jun 2003 13:38:25 +0200, Sybrand Bakker wrote:

>On Sat, 14 Jun 2003 12:50:48 +0530, Me <some...@despammed.com> wrote:
>
>>On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
>>>2) How can I put a check constraint that allows entering data into apointee only
>>>if the nominee's age is lesser than 18?
>>
>>Could someone help me with this constraint.
>>
>>Thanks.
>
>
>Trying to avoid to read the manual are we?

There was a time when the UNIX man pages were like a horror movie for me, and
now there is a time when I can't help seeing the man pages for every small
problem I have. In the beginning no one can understand the manuals.

Now lets see if someone can learn sed,awk or perl commands by using the manual.

Try
man find
man perl

>decode(sign(<age_column> - 18), 1, false, 0, false, true)

Frankly I am not able to make out what this is, but I don't think it can solve
the problem as what ever you do must involve 2 relations and 2 columns.

Thanks.

andrewst

unread,
Jun 14, 2003, 9:09:53 AM6/14/03
to

Originally posted by andrewst
> ALTER TABLE appointee

> ADD CONSTRAINT chk_nominee_age CHECK (age < 18);
Of course, Age is a rather silly column to hold in a table, because it
is meaningless by itself. Without knowing the date when the Age value
was entered/updated, it tells you only the the person is "at least" 17
(e.g.); by now he.she could be 18, 19, 42, ... What you should really
hold is date of birth (or year of birth or whatever suits your needs).

andrewst

unread,
Jun 14, 2003, 9:46:44 AM6/14/03
to

Originally posted by Me
> On Sat, 14 Jun 2003 11:33:48 +0000, andrewst wrote:
>
> >Originally posted by Me
> >> On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
> >> >2) How can I put a check constraint that allows entering
> data
> >> into apointee only
> >> >if the nominee's age is lesser than 18?
> >> Could someone help me with this constraint.
> >ALTER TABLE apointee

> >ADD CONSTRAINT chk_nominee_age CHECK (age < 18);
>
> No, that would not work. What I mean is this:
> There are two separate relations by name nominee and apointee; a
> policy holder
> will always have a nominee, if the nominee is a minor only then he
> will have an
> apointee whose age _will_ have to be greater than 18.
>
> At the same time the proposer's nominee's age must be lesser than
> 18 (minor)
>
> Thanks.
In that case, you cannot use a check constraint. A check constraint can
only compare values from ONE row of ONE table. You will have to write a
trigger, or encapsulate the rule in an API procedure.

Ed Prochak

unread,
Jun 14, 2003, 12:08:46 PM6/14/03
to

Well, your requirement doen't sound like a check constraint, but a higher
level business rule. You need to enforce it in the application layer.
Constraints cannot solve ALL these problems, and you don't want to be changing
the DB each time a rule like this changes.


--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Sybrand Bakker

unread,
Jun 14, 2003, 1:42:17 PM6/14/03
to
On Sat, 14 Jun 2003 18:21:30 +0530, Me <some...@despammed.com> wrote:

>Frankly I am not able to make out what this is, but I don't think it can solve
>the problem as what ever you do must involve 2 relations and 2 columns.


Thank you for posting an inaccurate description.
I won't waste my time anymore on your 'I don't want to read the manual
posts'

PLOINK

Me

unread,
Jun 14, 2003, 2:54:10 PM6/14/03
to
On Sat, 14 Jun 2003 16:08:46 GMT, Ed Prochak wrote:

>Me wrote:
>> On Sat, 14 Jun 2003 11:33:48 +0000, andrewst wrote:
>>
>>
>>>Originally posted by Me
>>>
>>>>On Fri, 13 Jun 2003 21:33:43 +0530, Me wrote:
>>>>
>>>>>2) How can I put a check constraint that allows entering data
>>>>
>>>> into apointee only
>>>>
>>>>>if the nominee's age is lesser than 18?
>>>>
>>>>Could someone help me with this constraint.
>>>
>>>ALTER TABLE apointee
>>>ADD CONSTRAINT chk_nominee_age CHECK (age < 18);
>>
>>
>> No, that would not work. What I mean is this:
>> There are two separate relations by name nominee and apointee; a policy holder
>> will always have a nominee, if the nominee is a minor only then he will have an
>> apointee whose age _will_ have to be greater than 18.
>>
>> At the same time the proposer's nominee's age must be lesser than 18 (minor)
>>
>> Thanks.
>>
>
>Well, your requirement doen't sound like a check constraint, but a higher
>level business rule. You need to enforce it in the application layer.
>Constraints cannot solve ALL these problems, and you don't want to be changing
>the DB each time a rule like this changes.

I am validating this from VB but I wanted to know if I could do this form SQL as
well.

Thanks.

Me

unread,
Jun 14, 2003, 2:55:38 PM6/14/03
to
On Sat, 14 Jun 2003 19:42:17 +0200, Sybrand Bakker wrote:

>On Sat, 14 Jun 2003 18:21:30 +0530, Me <some...@despammed.com> wrote:
>
>>Frankly I am not able to make out what this is, but I don't think it can solve
>>the problem as what ever you do must involve 2 relations and 2 columns.
>
>
>Thank you for posting an inaccurate description.
>I won't waste my time anymore on your 'I don't want to read the manual
>posts'
>
>PLOINK

I never said that I don't want to read the manual I just said that it is not
helping me right now.

Ed Prochak

unread,
Jun 15, 2003, 9:34:15 PM6/15/03
to

Well, I didn't say you could not do it in the Database, I just said you cannot
do it in a constraint. Have you considered adding triggers (PL/SQL) to check
rules like this? If at all possible I suggest you make them table driven, but
you may be happy with just a simple BEFORE INSERT trigger.

But it you already validated this, why add it to the DB? Likely you intend to
remove the VB validation, but then you will have to add code to handle bad
inserts (which return errors when the rule is violated).

HTH,

andrewst

unread,
Jun 16, 2003, 5:38:09 AM6/16/03
to

Originally posted by Ed Prochak
> But it you already validated this, why add it to the DB? Likely you
> intend to
> remove the VB validation, but then you will have to add code to
> handle bad
> inserts (which return errors when the rule is violated).
>
Probably because enforcing business rules through the client only
is bad practice and should be avoided if at all possible.

andrewst

unread,
Jun 16, 2003, 8:16:41 AM6/16/03
to

Originally posted by Me
> On Sat, 14 Jun 2003 13:09:53 +0000, andrewst wrote:
>
> >Originally posted by andrewst
> >> ALTER TABLE appointee
> >> ADD CONSTRAINT chk_nominee_age CHECK (age Of course, Age is a

> rather silly column to hold in a table, because it
> >is meaningless by itself. Without knowing the date when the Age
> value
> >was entered/updated, it tells you only the the person is "at
> least" 17
> >(e.g.); by now he.she could be 18, 19, 42, ... What you should
> really
> >hold is date of birth (or year of birth or whatever suits your
> needs).
>
> But the date on which the proposal was submitted has been stored in
> PROP_POLICY_DATA->PROP_DATE
>
> Moreover it is the age of the proposer at the time submitting the
> proposal that
> is crucial. Then the policy may be alloted for a period of 5 or 10
> years during
> which the actual age of the proposer is immeterial.
>

In that case fair enough perhaps.

Originally posted by Me
> I had sent you a mail but it bounced back.
> Please answer this post by mail rather than answering it in the group
> if it's ok
> with you.
> Thanks.
I have not answered by email. Apart from any other reasons, I don't
know your email address.

Ed prochak

unread,
Jun 16, 2003, 12:52:40 PM6/16/03
to
andrewst <membe...@dbforums.com> wrote in message news:<3006312.1...@dbforums.com>...

Agreed. But given that the OP already has this validation coded in the
VB front end and then wanted to force this into a check constraint, I
expected them not to understand suggestions about building rules
tables and such. Maybe I underestimated their skills.

(Interesting the way threads go sometimes. You try to explain at a
lower level and someone else at a higher level points out how you
simplified too much! No offense andrew)

Ed

andrewst

unread,
Jun 16, 2003, 3:28:04 PM6/16/03
to

Originally posted by Me
> On Mon, 16 Jun 2003 12:16:41 +0000, andrewst wrote:
> >Originally posted by Me
> >> I had sent you a mail but it bounced back.
> >> Please answer this post by mail rather than answering it in the
> group
> >> if it's ok
> >> with you.
> >> Thanks.
> >I have not answered by email. Apart from any other reasons, I
> don't
> >know your email address.
>
> My e-mail address is the same that I am using. some...@despammed.com
> no need to make any change. Just hit the reply button and you can
> send me a
> mail.
Really? some...@despammed.com just looks so unlike a real email
address! Anyway, I haven't any further comments to make at the moment.

andrewst

unread,
Jun 16, 2003, 3:26:19 PM6/16/03
to

Originally posted by Ed Prochak
> andrewst wrote in message news:news:...
None taken! I know how easy it is for a well-meant response to be taken
badly and escalate into a grudge match. I suppose that's why we need to
keep smiling and winking like this ;)

Sybrand Bakker

unread,
Jun 15, 2003, 6:47:38 PM6/15/03
to

"Me" <some...@despammed.com> wrote in message
news:ip4pev0csp1mogd93...@4ax.com...

> On Sat, 14 Jun 2003 13:09:53 +0000, andrewst wrote:
> But the date on which the proposal was submitted has been stored in
> PROP_POLICY_DATA->PROP_DATE
>
> Moreover it is the age of the proposer at the time submitting the proposal
that
> is crucial. Then the policy may be alloted for a period of 5 or 10 years
during
> which the actual age of the proposer is immeterial.
>
> If you have any other suggestions please mail them to me.
>


Time to learn how to design a datamodel. But, geez, that requires *reading*


--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address


Billy Verreynne

unread,
Jun 17, 2003, 5:00:24 AM6/17/03
to
Ed Prochak wrote:

> Well, your requirement doen't sound like a check constraint, but a higher
> level business rule. You need to enforce it in the application layer.
> Constraints cannot solve ALL these problems, and you don't want to be
> changing the DB each time a rule like this changes.

*NO!!!*

What is easier?

Changing the db layer? I.e. changing a PL/SQL stored proc, or a constraint
or creating a view?

Or change the front-end client? Then change the Corba C IDL on the client?
Then change the Corba code?

Then pray that it works and that you have not screwed up anything in any of
the layers?

Okay, let's make it simpler. Throw away the middle tier and deal with the
client only.

New business rule. Column END_DATE in table FOO must be larger than the 1st
of Jan 2003. Update the client. Then distribute the update to 10,000 users.

Only.. what happens to the old users who due to some problem do not get the
update? And keep on running the old software with the old business rule?
App versioning you say? Great. Then you will have 5,000 users calling
support complaining that they can not do their work as their app version is
no longer valid and they have not yet received the new update (as the
corporate LAN is hammered by app distribution).

Then you have other departments jumping up and down as the LAN is so
bottlenecked, they can not do their work either.

The client side should deal with data/information presentation. That is what
is is there for. INTERACTION between the database and the user. Not for
enforcing data integrity. And especially not for enforcing business rules.

Enforcing business rules in client software IS *STUPID*. Period.

I sometimes wonder if there are only a few of us here that actually work in
the friggen REAL WORLD.

Or else you guys must be smoking something. If so.. please pass some along
my way.

--
Billy

Ed Prochak

unread,
Jun 17, 2003, 8:39:59 PM6/17/03
to
Billy Verreynne wrote:
> Ed Prochak wrote:
>
>
>>Well, your requirement doen't sound like a check constraint, but a higher
>>level business rule. You need to enforce it in the application layer.
>>Constraints cannot solve ALL these problems, and you don't want to be
>>changing the DB each time a rule like this changes.
>
>
> *NO!!!*
>
> What is easier?
>
> Changing the db layer? I.e. changing a PL/SQL stored proc, or a constraint
> or creating a view?
>
> Or change the front-end client? Then change the Corba C IDL on the client?
> Then change the Corba code?

Where did I say front end client? Application layer really isn't the right
term, but my point was his rule could not be done in a check constraint!

[rest of a valid rant deleted.]


> The client side should deal with data/information presentation. That is what
> is is there for. INTERACTION between the database and the user. Not for
> enforcing data integrity. And especially not for enforcing business rules.

Agreed.

>
> Enforcing business rules in client software IS *STUPID*. Period.

I did suggest that since he ALREADY had coded the rule in his front end, that
he might leave it there for now. Not the greatest choice, but the code already
exists. He can add rules enforcement to the DB later and eventually remove
this check in the front end.

>
> I sometimes wonder if there are only a few of us here that actually work in
> the friggen REAL WORLD.
>
> Or else you guys must be smoking something. If so.. please pass some along
> my way.
>
> --
> Billy

Chill out.

Billy Verreynne

unread,
Jun 18, 2003, 3:34:41 AM6/18/03
to
Ed Prochak wrote:

> Where did I say front end client? Application layer really isn't the right
> term, but my point was his rule could not be done in a check constraint!

Ah.. get your terminology right Ed and you will prevent me from foaming at
the mouth.

(Application layer = front-end) in my mind.

>> Enforcing business rules in client software IS *STUPID*. Period.
>
> I did suggest that since he ALREADY had coded the rule in his front end,
> that he might leave it there for now.

Ah.. but you should have insulted his choice in architecture in the same
breath. As you did not, he likely thinks he made the right decision. :-)

> Not the greatest choice, but the
> code already exists. He can add rules enforcement to the DB later and
> eventually remove this check in the front end.

In my experience, that type of thing _never_ happens. Reason. It works. Kind
of. So never mind how inflexible and problematic it is.. it works so it
stays as is. I have first hand experience of this. And loads of it. And
then the DBA or developer doing the maintanence sit with The Problem..
because some dork has no idea on how to *correctly* use client-server
architecture.

>> I sometimes wonder if there are only a few of us here that actually work
>> in the friggen REAL WORLD.
>>
>> Or else you guys must be smoking something. If so.. please pass some
>> along my way.
>

> Chill out.

Only if you pass whatever your smoking/drinking.

Or are you telling me that dealing with crap like this in the real world on
mission critical systems are fine? That having to work till the wee hours
of the morn because of crap designs and development, is fine? (how many of
these have you pulled recently Ed?)

Sorry, but I can not let go of the old and trusted lead pipe when I run into
people who still insist on coding and designing crap. Or when people ignore
this crap and pretend that is fine designing and developing that way.

<humming>
I have a lead pipe
and I'm okay
I like beating crap
outta dorks everyday..

(chorus anyone?)

--
Billy

Sybrand Bakker

unread,
Jun 18, 2003, 4:16:29 PM6/18/03
to

"Billy Verreynne" <vsl...@onwe.co.za> wrote in message
news:bcotpm$hjt$1...@ctb-nnrp2.saix.net...

> Ed Prochak wrote:
>
> Or are you telling me that dealing with crap like this in the real world
on
> mission critical systems are fine? That having to work till the wee hours
> of the morn because of crap designs and development, is fine? (how many of
> these have you pulled recently Ed?)
>

Apparently you are surrounded by Saints.
I do this all the time. I'm not aware any of my customers is abiding by the
usual rules.
Tables without primary keys: happens regularly
Foreign keys: please explain me what they are, never heard of it.
We can't influence the SQL, or development environment is generating it.
Or some dork by means of a tool on a website managed to delete half of the
database (only 1 schema in that database, tools connects as the owner of the
schema)
Who do you think had to repair that? The customer?
Or some other dork that pulled the plug of the system *before* the UPS, just
when the server was shutting down. Any idea what happened?
You are in paradise and you shouldn't complain.

Billy Verreynne

unread,
Jun 19, 2003, 4:28:53 AM6/19/03
to
Sybrand Bakker wrote:

> Apparently you are surrounded by Saints.

No. Which is why I foam at the mouth so easily.

> Tables without primary keys: happens regularly
> Foreign keys: please explain me what they are, never heard of it.

Well.. how about a 7 column primary (with nulls and PK columns that change)?
Then going and indexing those 7 columns in various pairs and orders over
again. 5 times. Something like index1(col1,col2) and index2(col2,col1).

> We can't influence the SQL, or development environment is generating it.

Yeah.. and that is utterly frustrating.

> Or some dork by means of a tool on a website managed to delete half of the
> database (only 1 schema in that database, tools connects as the owner of
> the schema)

Hehehe.. well, how about the production schema who owns all production
tables and has been giving DBA privs? (before my time btw). And then have
_everyone_ know the schema password as it has been hardcoded into certain
batch Pro*C programs and can not be changed.

> Who do you think had to repair that? The customer?

We only have to look in the mirror to see that poor sucker.

> Or some other dork that pulled the plug of the system *before* the UPS,
> just when the server was shutting down. Any idea what happened?

Don't spare me Sybrand.. I can take it...

> You are in paradise and you shouldn't complain.

Yeah maybe.. The grass always look greener on the other side.

What really bugs me (and which is why I ranted at Ed), is that having Oracle
skills in development process is critical. Yet, the norm is still to treat
the database as a black box. Throw tables into it. Stir vigorously and
serve.

The tables in the mix are not normalised. There is no physical db design.
They never heard of an IOT, bitmap index or any of the other tools in the
toolbox. But then when all problems are decreed to be nails, you only need
The Hammer from the toolbox. <sigh>

--
Billy

Ed Prochak

unread,
Jun 19, 2003, 10:50:34 PM6/19/03
to
Billy Verreynne wrote:
> Ed Prochak wrote:

>>>Enforcing business rules in client software IS *STUPID*. Period.
>>
>>I did suggest that since he ALREADY had coded the rule in his front end,
>>that he might leave it there for now.
>
>
> Ah.. but you should have insulted his choice in architecture in the same
> breath. As you did not, he likely thinks he made the right decision. :-)

Yes, maybe I should have been meaner. 8^)

>
>
>>Not the greatest choice, but the
>>code already exists. He can add rules enforcement to the DB later and
>>eventually remove this check in the front end.
>
>
> In my experience, that type of thing _never_ happens. Reason. It works. Kind
> of. So never mind how inflexible and problematic it is.. it works so it
> stays as is. I have first hand experience of this. And loads of it. And
> then the DBA or developer doing the maintanence sit with The Problem..
> because some dork has no idea on how to *correctly* use client-server
> architecture.

Well it sometimes makes more work for the good guys (ie those who know how to
do it right). If you are a good guy that gets paid by the hour, you win. 8^)

>
>
>>>I sometimes wonder if there are only a few of us here that actually work
>>>in the friggen REAL WORLD.
>>>
>>>Or else you guys must be smoking something. If so.. please pass some
>>>along my way.
>>
>>Chill out.
>
>
> Only if you pass whatever your smoking/drinking.

Well, I'll send over a little from a gift my son got me for Father's day:
Everclear (190PROOF)
Just don't open the sample near a flame!

>
> Or are you telling me that dealing with crap like this in the real world on
> mission critical systems are fine? That having to work till the wee hours
> of the morn because of crap designs and development, is fine? (how many of
> these have you pulled recently Ed?)

Actually I may be doing something like that soon (cleaning up some poor
designs). Whether wee hours will be involved is yet to be seen. I came across
exactly this problem with front end code in my current job. If I'm part of the
original developers, I fight to keep such crap out. If I'm a maintainer (as I
am currently), I'll fight to get rid of it. I agree, when possible, it's
better to do it right than to keep patching a poor design.

>
> Sorry, but I can not let go of the old and trusted lead pipe when I run into
> people who still insist on coding and designing crap. Or when people ignore
> this crap and pretend that is fine designing and developing that way.
>
> <humming>
> I have a lead pipe
> and I'm okay
> I like beating crap
> outta dorks everyday..
>
> (chorus anyone?)
>
> --
> Billy

doesn't it go like:

I work all night and I sleep all day
I wear women's clothing < mumble words I for got >
and walk around in bars
8^)

wait, that anothe verse.

Funniest argument I've had in years. Thanks Billy.

Ed Prochak

unread,
Jun 19, 2003, 11:03:35 PM6/19/03
to
Billy Verreynne wrote:
[]

>
> Yeah maybe.. The grass always look greener on the other side.
>
> What really bugs me (and which is why I ranted at Ed), is that having Oracle
> skills in development process is critical. Yet, the norm is still to treat
> the database as a black box. Throw tables into it. Stir vigorously and
> serve.
>
> The tables in the mix are not normalised. There is no physical db design.
> They never heard of an IOT, bitmap index or any of the other tools in the
> toolbox. But then when all problems are decreed to be nails, you only need
> The Hammer from the toolbox. <sigh>
>
> --
> Billy

BTW, Billy,

in my current job, I'm back using UNIFY. Man, was I spoiled with ORACLE's
multiversioning! The biggest problem here seems to be locking! Never had to
think about that in ORACLE (well in a few SELECT...FOR UPDATE statements on
rare occasions).

There is a lot that should be black box to some levels, you clearly see where.
I do agree with you Billy (and take no offense at your rant earlier). Also a
good design lends itself to easy enhancement in features and performance.

I'll leave you with this thought:
Sometimes you feel like whale shit at the bottom of the ocean,...
and sometimes you don't feel that good!

Enjoy. Maybe the point did get across to a few.
A good design is worth fighting for.

Joel Garry

unread,
Jun 20, 2003, 7:33:03 PM6/20/03
to
Ed Prochak <edpr...@adelphia.net> wrote in message news:<3EF27DF6...@adelphia.net>...

> Billy Verreynne wrote:
> []
> >
> > Yeah maybe.. The grass always look greener on the other side.
> >
> > What really bugs me (and which is why I ranted at Ed), is that having Oracle
> > skills in development process is critical. Yet, the norm is still to treat
> > the database as a black box. Throw tables into it. Stir vigorously and
> > serve.
> >
> > The tables in the mix are not normalised. There is no physical db design.
> > They never heard of an IOT, bitmap index or any of the other tools in the
> > toolbox. But then when all problems are decreed to be nails, you only need
> > The Hammer from the toolbox. <sigh>
> >
> > --
> > Billy
>
> BTW, Billy,
>
> in my current job, I'm back using UNIFY. Man, was I spoiled with ORACLE's
> multiversioning! The biggest problem here seems to be locking! Never had to
> think about that in ORACLE (well in a few SELECT...FOR UPDATE statements on
> rare occasions).

Omigod, I've come unstuck in time!
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&newwindow=1&th=bb576d594e3f8b61&rnum=2

>
> There is a lot that should be black box to some levels, you clearly see where.
> I do agree with you Billy (and take no offense at your rant earlier). Also a
> good design lends itself to easy enhancement in features and performance.
>
> I'll leave you with this thought:
> Sometimes you feel like whale shit at the bottom of the ocean,...
> and sometimes you don't feel that good!
>
> Enjoy. Maybe the point did get across to a few.
> A good design is worth fighting for.

I remember posting something like "Welcome to the world of referential
integrity!" in the early V7 time frame in response to someones
question, and in the V6 timeframe unify was ahead of oracle in that
respect. Couldn't find google it though, maybe it was on Compuserve.

jg
--
@home.com is bogus.
Maybe there is no usenet, and we are all dreaming the same dream.

Tim X

unread,
Jun 22, 2003, 1:28:49 AM6/22/03
to
>>>>> "Ed" == Ed Prochak <edpr...@adelphia.net> writes:

Ed> doesn't it go like:

Ed> I work all night and I sleep all day I wear women's clothing <
Ed> mumble words I for got > and walk around in bars 8^)

Ed> wait, that anothe verse.

Ed> Funniest argument I've had in years. Thanks Billy.

We didn't come here for an argument...

we came here to complain.

--
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you
really need to send mail, you should be able to work it out!

Billy Verreynne

unread,
Jun 23, 2003, 10:52:09 AM6/23/03
to
Tim X wrote:

> Ed> Funniest argument I've had in years. Thanks Billy.
>
> We didn't come here for an argument...
> we came here to complain.

Will that be a the $5 compliant or the $10 compliant? Or the $15 special
that gives you 4 compliants for the price of 3?

Please note that complaining that you hovercraft is full of eels will
automatically cause a surcharge of $3.

--
Billy

Reply all
Reply to author
Forward
0 new messages