4

Let's say I have a table like this:

CREATE TABLE dept (
  id     VARCHAR(255) NOT NULL PRIMARY KEY,
  code   VARCHAR(255) NOT NULL,
  active BIT          NOT NULL,
  ...
);

Problem:

I want to add a unique constraint on code column. But it should be applied only if active is set to true (uniqueness should be checked only among active records). There can be many records with active = false and the same code so I can't use constraint on multiple columns.

What I tried:

I haven't found any references in the documentation proving that such constraint is possible, but I know it is possible in other databases using unique function-based indexes.

Of course I can write a trigger that will check the invariant on every add/update operation, but I hope there is more efficient solution.

I'm using MySQL 5.7.15.

Improve this question
2
  • This simply isn't possible in MySQL, I'm afraid. The closest you could perhaps come is to have a uniquely constrained column which is nullable (replacing the active field). When NULL - it's "inactive", when anything other than NULL - it has to be unique. Otherwise read/write to the table through a stored procedure or do something inelegant with triggers. Commented Nov 29, 2017 at 10:15
  • Possibly there's something more elegant, but it'd require us knowing the bigger picture (that is to say - the overall problem you're trying to solve). Could you update your question with the Big Picture(TM)? Commented Nov 29, 2017 at 10:20

4 Answers 4

Reset to default
5

This simply isn't possible in MySQL, I'm afraid.

I have come "close" to solving this in the past by having a uniquely constrained column which is nullable (replacing both the active and code fields). When NULL - it's "inactive", when anything other than NULL - it has to be unique.

But that doesn't precisely solve the problem you're asking. (Perhaps something better can be suggested if you could update your question to include the bigger picture?)

Otherwise read/write to the table through a stored procedure or - as you've suggested yourself - do something inelegant with triggers.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I can't set code to NULL for inactive records - it is still business valuable information (for instance the record could later be moved back to active state)
Ah OK - fair enough. Without seeing the bigger picture then, I think you're probably stuck having a transactionally safe procedure for read/writing to the table which updates a separate column with a unique constraint. Unfortunately as good as MySQL is, it doesn't have a lot of functionality that some of the other DBMSes do... :-)
That's sad true. Probably MySQL wasn't the best choice we made, but unfortunately it is too late to change. This is already the 4th important feature in my list which is missing in MySQL but present in other free DBs. On the other hand who knows, may be there might be some other important features present in MySQL and missing in those DBs.
I like to think of MySQL as "having everything someone needs from a DBMS ... but lacking a ton of things someone wants." It's a superb platform, with transactional safety, incredible performance, replication, permissions, etc. But for a lot of situations you have to work harder to get to what you want.
1

To solve your problem you need use CHECK clause but it MySQL don't support it. From doc:

The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.18, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references.

So you can do this only by check data on application level or insert/update rows in this table by stored procedures.

Improve this answer

Comments

1

I sorry this does not really a direct answer your question but:

Maybe you are better off with a different table design? The fact that something you want to do is not supported by your RDBMS is always a strong evidence that you are using it wrong.

Have you thought about creating a dept and an dept_history table, dept containing only the active records? That would solve your problem with the unique constraint.

Improve this answer

3 Comments

It is overhead. The fact that MySQL doesn't support CHECK clause doesn't mean that DB design is wrong - almost all other DB provides do support this.
Just offering an alternative approach here. I can´t judge you DB-Design, as I don´t know it, but you are certainly wanting to use MySQL (The RDBMS) beyond its capabilities. What kind of overhead do you think does the check-clause introduce into an RDBMS that does support it?
Of course it is trade-off. There were reasons for sure. Bot not having such feature out of the box shifts the responsibility to a developer, who will for sure implement it in a less efficient way. I'd like to have the feature with all it's overheads/consequences rather than implement it by myself.
1

You can create functional index:

unique index ((if(active, code, null)))
Improve this answer

1 Comment

Thank you. Could you please elaborate more on how it works?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.