Can a linking table have two mutually exclusive columns?_问答_开发者

I have a problem where, due to previous people's poor design decisions, I'm forced to have two columns in a linking table that should always be mutually excl开发者_开发技巧usive. I've seen SQL-Server: Define columns as mutually exclusive but MySQL doesn't enforce CHECK constraints so I'm looking for other options. Is there any way to enforce mutual exclusion between two columns?

Let me explain in case someone sees a better solution.

Previous dev/admins have constructed two tables that record basically the same type of things. I have a new a table that is 1 -> many in theory to the thing they both represent. So my two options seem to be to create either two bridging tables or 1 bridging tables with two external key references. The two tables seems like an ugly solution to me that I was hoping to avoid but unenforced mutually exclusive fields leaves me open to easy corruption.

Concrete example since I can't share my real case:

Table 'full_time_students' stores full time students
Table 'part_time_students' store part time students

Table 'class' will store class info
Table 'class_students' should store which students are in which class

In an ideal world, I would combine the two student tables but there's a lot of code that would break and I don't have the time/will to go fix all of that so I'm forced for now to deal with the existent schemas/data. I'm using InnoDB so I was already planning on having foreign key constraints on the student_ids but like I said, if I do one table with two columns I open myself up to the corruption of having one row with both ids.

Just writing this out almost convinces me to reconsider the 2 table solution...

Reworked in Response to Comments.

I do not agree with any of the answers.

There is nothing to be afraid of with "more" tables, that is the nature of a Relational database; especially if you want the power of Relational, and to allow users access the database without having to go through your app.

I am assuming two things:

You think that the previous devs made a mistake in that the StudentFullTime and StudentPartTime should be amalgamated into Student, because they have many common columns
You have a new requirement Class that needs a many-to-many relation between Class and Student.
- In relational terminology, that's called an Associative table: in the logical model, it is shown as a n::n relation (no table); in the physical model, it is rendered as a table. (Of course, if it has columns, then it would show up in the logical model as an Entity, not a relation.)

Ok, (1) is incorrect. Just as it is wrong to have common columns that have not been Normalised into a single table, it is also wrong to have a single table with columns that are not applicable (Nullable columns). What they should have done is implement three tables; not one; and not two, in an ordinary subtype-supertype cluster.

The supertype contains the common columns plus a Differentiator, and each subtype contains the columns which are private to it. The relation between the supertype and the subtypes is one-to-one. This is very powerful, because (a) it eliminates ambiguities (which would be present in a single unnormalised table), (b) eliminates Nulls and (c) most important, allows any indices on those private columns to be simple and unique (due to lack of nulls).
- All my Data Models comply with the IDEF1X standard. To assist people who are new to IDEF1X, here is a document that explains IDEF1X Notation document. If you do not understand what the circles, bars, and crows feet mean; or what Is on the Relation line means; or why the horizontal line is where it is, please read that now.

Normalised Before

So you come along, and you need to implement (2). Life would have been easy for all concerned, because the database is Normalised: you can add what you need without having to change existing table structures, or upsetting any existing code.

Normalised After

But they did not do that, they left two Unnormalised tables in the "database".

So you come along, and you need to implement (2). In seeking "mutually exclusive" columns, you have identified a Normalisation error (on your side). The n::n Associative table you need is actually Classes that have Students; the fact that they may be FullTime or PartTime is a progression to worry about after you confirm the basic need. After you contemplate that need, sure, you have to implement something that handles both types of Students.

if you implement one table, it is a gross error, and you need a dbms that will do funny things like "mutual exclusion"
if you implement two tables, you are merely compounding their error. That will end up being limited, and your SQL code will be ugly.

Their Error Progressed by You

What is called for, is for you to Normalise, on your side, and therefore avoid hindering the power of the db, on your side.

Normalised Yours

One table for the Associative table, and one table each for the two subtypes (based on the existence of the two sub-standard tables). Simple subtype-supertype 1::1 cluster.
No Nulls, Full integrity, FKs, referenceability, etc. Simple unique indices on each table. Think about what your code will look like.
If not now, then in the future, when you need to add columns to the Associative table (just like you need to add a table today), you add them into one place. If you compounded their error, you have to place those columns in two places.

And please migrate the existing keys, they are quite adequate and they already have meaning, do not create new IDENTITY keys.

Here's a thought to fix the problem without breaking much code. Create a newtable students and populate with the data from both the other tables and a flag field to indicate full or part time. Then create views that have the names of the full time students and part time students table. This means all selects will still work perfectly and all you will have to do is fix the insert and update code to go to the new table.

Or you could enforce your rules in a trigger.

Enforcing business rules using triggers in MySQL has come up here before:

Throw an error in a MySQL trigger