Hi Team,
Is there any way to turn a Constraint On and Off?
I have two Customers using the same data schema.
One Customer wants the Constraint active, the other doesn't.
I don't want to have to manage different schemas if possible.

Regards & TIA,
Ian

Ian


Short answer - no (I think but I'll have a play)

Long answer -  sort of depending on how much effort you want to go to.

If it would be possible to move the constraint into a trigger then its easy because they can be turned on and off fairly easily.

What is the actual constraint? The type of constraint will affect what may be possible.

Roy Lambert

Roy Lambert wrote:

If it would be possible to move the constraint into a trigger then its easy because they can be turned on and off fairly easily.

What is the actual constraint? The type of constraint will affect what may be possible.

Hi Roy,
It's in the JobTickets table.  The combination of two fields, CustomerNo (Integer) & CustomerRef (VarChar,12,ANSI, Nullable) must be unique.

Regards,
Ian

On 9/5/2019 1:13 PM, Ian Branch wrote:
> It's in the JobTickets table.  The combination of two fields, CustomerNo (Integer) & CustomerRef (VarChar,12,ANSI, Nullable) must be unique.
>

You cannot turn constraint off dynamically - it's either present in
table data definition or removed (i.e. alter table).

However in your case what about populating it with fixed value for the
customer that does not want it ? (using either a "before insert" trigger
or even in your code when creating record).

Raul

Ian,

In general, no, you can't turn a constraint off - it wouldn't be a constraint if you could .
If it is a "check" constraint, it's perhaps possible to "cook" something changing the check conditions, but if it's a Foreign or Primary Key constraint, it would not make sense at all as it's a fundamental part of the definition of the data  schema.
You can, as already suggested, turn it into a check test in a trigger, but it's not exactly the same because the updates might not be atomic anymore, depending on how you design it.
Without more details we can't say more, I think...

--
Fernando Dias
[Team Elevate]

Ian
..
As the others indicate - no chance. Personally I'm on the side of the customer who doesn't want it. Generally speaking you have no control over customer sequence numbers / references. The only thing I can suggest is a before post trigger (or table event)  and raise an exception if the check isn't met. You can then build in a test for who's system it is and act accordingly.


Roy Lambert