CODEWITHSUNDEEP
oracle-databaseuser-defined-functionscheck-constraint
Alex
Alex

Reputation: 1

Oracle: Function Parameter - how to implement?

i have a little problem and i want to ask you for help :)

so to make it simple, im using an oracle database and i want to create an "check constraint" on one of my tables, i.e. below.

Table XY

Attribute A || Attribute B || Attribute C

for attribute A and B the user can add whatever he wants - and for attribute C i want to use a "check constraint" using a user defined function - which checks if the combination of A and B is valid or not.

my problem is, i have no idea how to implement the input parameters for the function so i can make a check while the user creates the entry in the database.

in other words, the user already added 1 to A and 3 to B and once he wants to add an entry for C i want to (example) check if A + B = 4

i hope you can help me, because im going bananas right now x)

kind regards alex

EDIT (copied from comment below)

I have 3 tables:

  1. GROUP
  2. GROUP_LIST
  3. TM

GROUP includes the ID of a GROUP, GROUP_LIST is the connection table between the GROUP and the TM - so in GROUP_LIST I connect a GROUP with a TM - but it's possible to connect 1 GROUP with several TMs f.e.:

GROUPID || TM
1       || 1
1       || 2
1       || 3
2       || 1

and so on and my real issue is following: now, I want a check on the TM attribute which checks if the TM I'm trying to fill in, is already existing in the same GROUPID. I hope now its clear what my intention is ...

Upvotes: 0

Views: 113

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

It seems rather pointless to require users to enter a 'correct' value for a particular column. Instead of defining C as a normal column I suggest defining it as a computed column, e.g.:

CREATE TABLE TBL
  (A    NUMBER,
   B    NUMBER,
   C    AS (A + B));

In this way C will always be computed correctly.

SQLFiddle here

Best of luck.

EDIT

Based on information from OP in the comment below it appears that this answer doesn't address the real need so I've added a second answer (below). I considered deleting this answer but because it contains the comment from OP which adds important information (which I've since edited into the question) I decided to leave this answer in place, but to forfeit the rep gain by making this answer Community Wiki.

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

Based on new information from OP it appears that the correct way to solve this issue would be to add a UNIQUE constraint to the GROUP_LIST table to ensure that the combination of GROUP_ID and TM is unique:

ALTER TABLE GROUP_LIST
  ADD CONSTRAINT GROUP_LIST_UNIQUE_1
    UNIQUE (GROUP_ID, TM);

Best of luck.

Upvotes: 0

dbra
dbra

Reputation: 631

If you use ouf-of-line check constraint syntax you could define a condition with any of the fields of the specific table, something like:

ALTER TABLE xy ADD CONSTRAINT mymulticheck CHECK (A + B = C);

Note that you have many restriction on this type of constraint, as example you cannot use a user defined function, and obviously the condition is fixed and identical for all the rows.

Upvotes: 1

Related Questions