Adding a user-defined constraint to an SQL table

Question

So i'm working with 2 tables: Persons and Marriage. Persons hold many attributes and his PK is person_id. Marrige is defined like that:

CREATE TABLE Marriage(Person_id NUMBER(6) PRIMARY KEY REFERENCES Persons(Person_id)
, Relative_id NUMBER(4) REFERENCES Persons(Person_id)
, Relationship_type VARCHAR(20) NOT NULL CHECK (Relationship_type IN('Wife', 'Husband', 'Child'))
)

I want to add a constraint to this table so ive added this (TODO:Parent.age>Child.age):

CREATE FUNCTION fn_OlderThanSon (
    @Parent NUMBER
    @Child NUMBER
)
RETURNS VARCHAR(10)
AS
BEGIN
    IF EXISTS (SELECT Person_id FROM Persons WHERE Person_id = @Parent)
AND EXISTS (SELECT Person_id FROM Persons WHERE Person_id = @Child)
        return 'True'
    return 'False'
END

now when I'm trying to attach it to the Marriage table doing this:

ALTER TABLE Marriage ADD CONSTRAINT CK_OlderThan 
CHECK (fn_OlderThanSon(Person_id,Relative_id) = 'True');

I get the following error:

java.sql.SQLSyntaxErrorException: ORA-01735: invalid ALTER TABLE option

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
    at com.company.Main.main(Main.java:93)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

so, what am I doing wrong? is it even possible to do such logic constraint?

Answer 1

The way you want this to work is not supported. You have several options though:

1. You could write a trigger which would be triggered before insert/update and do this check. If the result is undesirable, then throw an error.

2. You could do the insertion inside a stored procedure if according to the function everything is all right. And do the insertion through that stored procedure.

3. You could make checks at application level and only call the insert if everything is all right.

From a purely scientific point of view the combination of the second and third option is desirable. The first option will cause you headaches if you need to do some things urgently, knowing that the results will be all right.

However, you will need to cope with different scenarios as well. If a child is inserted/updated, you will need to check that the child will not be older than any of his/her parents besides comparing his age to his old child's age. Also, you might need to legitimately update the age of both the parent and the child, but then the order is not commutative.

Example: Parent is of 40 years of age, the child is 20 years old. You will need to update the parent to 61 and the child to 41. If you do this in the wrong way, that is, update the child to 41 years old when the parent's age was not changed from 40 yet, then the update via the trigger/stored procedure will not be successful according to your current business rule.

Answer 2

You want to use a function fn_OlderThanSon() to ensure a record of type 'parent' is older than a record of type 'child': if (Person_id.age>Relative_id.age).

Such a business rule requires an assertion - a declarative multi-row constraint - rather than a simple check constraint. Currently Oracle does not support CREATE ASSERTION (although there is an initiative to make this happen).

Right now, to enforce a rule like this requires procedural logic, which can be invoked directly with a PL/SQL API over the Persons table or indirectly through a trigger. The trigger will be complicated, because you have to work around the mutating table issue (you need to query records in the table you're actively changing) but many people don't like shielding tables behind PL/SQL procedures.