CODEWITHSUNDEEP
constraintsrdbms
Stephan K.
Stephan K.

Reputation: 15712

Define DB Constraint

Is it possible to define a constraint like this:

A <- SELECT Enrolment course = x AND student = y
B <- PROJECT A OVER student
COUNT(B) < 3

I'm particularly interested in the "AND" part. I know from my books that the rest is OK.

The syntax is based on Codd [1] and does not apply to any particular DBMS.

Sources:

  1. http://en.wikipedia.org/wiki/Relational_algebra

Upvotes: 0

Views: 133

Answers (1)

paxdiablo
paxdiablo

Reputation: 881553

With a constraint, no, not in general. That's a little complex for a constraint.

If you want something like that, you usually have to resort to triggers, which can usually run arbitrarily complex code. A before-insert and before-update trigger, assuming your DBMS has them, should do the trick.

Please note that triggers almost certainly have a performance impact and they should only be used where necessary but they should be used if the data has to follow rules, don't let anyone tell you differently).

The general rule is: if the constraint is a property of your data, it should be done by the DBMS itself (with constraints or triggers or whatever vendor-specific things you may have available to you).

However, if the constraint is a property of the application, it should be handled by the application without any effort by the DBMS.

In reality, I don't think I've ever seen that latter situation arise since the application and data tend to be bound pretty tightly, but I've never seen little green men either and they may well exist :-)

Upvotes: 1

Related Questions