Hi Wing,
The utility Oracle offers for that is *check constraints*.
For instance :
ALTER TABLE Demerit ADD CONSTRAINT a1 CHECK (points between 1 and 6);
ALTER TABLE Demerit ADD CONSTRAINT a2 CHECK (dem_code <= 20 or
dem_code 20 and points <=3);
Will implement the rules you mention.
Also,
ALTER TABLE Demerit ADD CONSTRAINT a3 CHECK ( c2 between 1 and 6 and
(c1 <= 20 or c220 and c2 <=3) );
will implement a1 and a2 above in one constraint, but is less
readable.
Read the doc to get the full syntax and full *understanding* of check
constraints. They are not good for all purposes, in fact misusing them
is equal to painting yourself into the corner.
Happy Christmas
- Kenneth Koenraadt wi******@witty. com (wing) wrote in message news:<87******* *************** ****@posting.go ogle.com>...
Hi,
I am new in Oracle and have a query on how to add conditional
constraint.
Say, I have a simple table Demerit with three fields.
Demerit(DEM_COD E, DEM_DES, POINTS)
where
DEM_CODE (N, 2)
DEM_DES (C, 30)
POINTS (N, 1)
How to add the following constraint?
All DEM_CODE should carry an integer POINT between 1 to 6 inclusive,
and DEM_CODE greater than 20 should carry no more than 3 POINT.
Thanks in advance of any ideas and inputs.
Wing
2 2511
Hi Wing,
I'm with Kenneth and the "constraint camp" on this one. This is most
appropriately resolved through the use of constraints rather than a
trigger. For a number of reasons. It's easier to code and handle error
conditions, it's potentially more efficient and the CBO may have more to go
on (although admittedly, it's not too smart when it comes to "complex" check
constraints).
The only thing I would add to what Kenneth and Jonathon have suggested
is that I would personally recommend going for option 1 (2 constraints)
rather than option 2 (1 complicated constraint). The reason being that
if the constraint were to be violated, if they were handled separately,
it would be obvious what the issue was. If you had them grouped
together, it might require further investigation to determine the exact
cause of the violation.
Merry Xmas everyone !!
Cheers
Richard
"Kenneth Koenraadt" <pl******@mai l-online.dkwrote in message
news:25******** *************** ***@posting.goo gle.com...
Hi Wing,
The utility Oracle offers for that is *check constraints*.
For instance :
ALTER TABLE Demerit ADD CONSTRAINT a1 CHECK (points between 1 and 6);
ALTER TABLE Demerit ADD CONSTRAINT a2 CHECK (dem_code <= 20 or
dem_code 20 and points <=3);
Will implement the rules you mention.
Also,
ALTER TABLE Demerit ADD CONSTRAINT a3 CHECK ( c2 between 1 and 6 and
(c1 <= 20 or c220 and c2 <=3) );
will implement a1 and a2 above in one constraint, but is less
readable.
Read the doc to get the full syntax and full *understanding* of check
constraints. They are not good for all purposes, in fact misusing them
is equal to painting yourself into the corner.
Happy Christmas
- Kenneth Koenraadt
wi******@witty. com (wing) wrote in message
news:<87******* *************** ****@posting.go ogle.com>...
Hi,
I am new in Oracle and have a query on how to add conditional
constraint.
Say, I have a simple table Demerit with three fields.
Demerit(DEM_COD E, DEM_DES, POINTS)
where
DEM_CODE (N, 2)
DEM_DES (C, 30)
POINTS (N, 1)
How to add the following constraint?
All DEM_CODE should carry an integer POINT between 1 to 6 inclusive,
and DEM_CODE greater than 20 should carry no more than 3 POINT.
Thanks in advance of any ideas and inputs.
Wing
Richard Foote wrote:
Hi Wing,
I'm with Kenneth and the "constraint camp" on this one. This is most
appropriately resolved through the use of constraints rather than a
trigger. For a number of reasons. It's easier to code and handle error
conditions, it's potentially more efficient and the CBO may have more to go
on (although admittedly, it's not too smart when it comes to "complex" check
constraints).
The only thing I would add to what Kenneth and Jonathon have suggested
is that I would personally recommend going for option 1 (2 constraints)
rather than option 2 (1 complicated constraint). The reason being that
if the constraint were to be violated, if they were handled separately,
it would be obvious what the issue was. If you had them grouped
together, it might require further investigation to determine the exact
cause of the violation.
Merry Xmas everyone !!
Cheers
Richard
"Kenneth Koenraadt" <pl******@mai l-online.dkwrote in message
news:25******** *************** ***@posting.goo gle.com...
Hi Wing,
The utility Oracle offers for that is *check constraints*.
For instance :
ALTER TABLE Demerit ADD CONSTRAINT a1 CHECK (points between 1 and 6);
ALTER TABLE Demerit ADD CONSTRAINT a2 CHECK (dem_code <= 20 or
dem_code 20 and points <=3);
Will implement the rules you mention.
Also,
ALTER TABLE Demerit ADD CONSTRAINT a3 CHECK ( c2 between 1 and 6 and
(c1 <= 20 or c220 and c2 <=3) );
will implement a1 and a2 above in one constraint, but is less
readable.
Read the doc to get the full syntax and full *understanding* of check
constraints. They are not good for all purposes, in fact misusing them
is equal to painting yourself into the corner.
Happy Christmas
- Kenneth Koenraadt wi******@witty. com (wing) wrote in message
news:<87******* *************** ****@posting.go ogle.com>...
Hi,
>
I am new in Oracle and have a query on how to add conditional
constraint.
>
Say, I have a simple table Demerit with three fields.
>
Demerit(DEM_COD E, DEM_DES, POINTS)
where
DEM_CODE (N, 2)
DEM_DES (C, 30)
POINTS (N, 1)
>
How to add the following constraint?
>
All DEM_CODE should carry an integer POINT between 1 to 6 inclusive,
and DEM_CODE greater than 20 should carry no more than 3 POINT.
>
Thanks in advance of any ideas and inputs.
>
Wing
Now that I can see the examples ... I too would agree. Go with the constraint
not the trigger.
Dan Morgan This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ney André de Mello Zunino |
last post by:
Hello.
I have noticed, in a lot of C and C++ code, that many programmers seem
to prefer putting the test values first in conditional expressions.
I.e., they would rather write this:
if (-1 == foobar())
than this:
|
by: Ilik |
last post by:
Hi all
I'm trying to create a summery table of rain data that holds a record
for every 0.1mm of rain in the following format:
Station Name, Time, Value
A, 2002-12-03 14:44:41.000, 0.1
A, 2002-12-03 14:45:45.000, 0.1
A, 2002-12-03 14:49:45.000, 0.1
A, 2002-12-09 05:30:35.000, 0.1
A, 2002-12-09 05:30:37.000, 0.1
|
by: pdlevine |
last post by:
Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
|
by: Benjamin Niemann |
last post by:
Hello,
I've been just investigating IE conditional comments - hiding things from
non-IE/Win browsers is easy, but I wanted to know, if it's possible to hide
code from IE/Win browsers.
I found <!> in the original MSDN documentation, but this is (although
it is working) unfortunately non-validating gibberish.
So I fooled around trying to...
|
by: ferg |
last post by:
I have a Customer table. The table has two different CHECK constraints.
Then there is the Customer details dialog, which provides the user with
an UI for changing users. I have some UPDATE sql, which is called once
the user clicks the OK button on this dialog.
try
{
int rows = cmd.ExecuteNonQuery();
}
catch(SqlException se)
| |
by: negmat |
last post by:
Guys,
Does SQL Server allow for creating conditional constraints on the table columns?
My table is:
CREATE TABLE
(
IDENTITY (1, 1) NOT NULL ,
|
by: askohen |
last post by:
I have this CREATE TABLE statement:
CREATE TABLE howto (
id INT IDENTITY(1,1) PRIMARY KEY,
title VARCHAR(100) UNIQUE NOT NULL,
url VARCHAR(20) UNIQUE NOT NULL,
order_on_homepage SMALLINT UNIQUE, --just put an order on this and if it's not null, then its on the homepage
CONSTRAINT CK_HOWTO_URL CHECK(url != ''),
CONSTRAINT...
|
by: DA Morgan |
last post by:
wing wrote:
What you are trying to do requires a trigger. I would suggest a
BEFORE INSERT AND UPDATE trigger that fires for each row.
I would also suggest that in the future you:
1. List your version and edition of Oracle
2. NEVER EVER post to more than a single usenet group unless you are
trying to get people so angry they won't help...
|
by: rorajoey |
last post by:
Violation of UNIQUE KEY constraint 'IX_surveyQuestions'. Cannot insert duplicate key in object 'dbo.surveyQuestions'.
This might seem like a simple matter of trying to insert a row with ID=20 when there's already one with that ID, but the problem is a bit more complicated. The table is supposed to auto-increment the value for the primary key...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |