create table constraint in mysql

Question

I have a table in which I have some columns a,b,c and for each column there is another column ,say, (x,y,z) which is dependent on a,b,c respectively.

x,y,z will have value 1 if a,b,c has any value and will contain null if a,b,c has null.

For an example Lets say, The values stored in a is 2 and x is the column dependent on it. So x will have value as 1.

If the values stored in a is null then x will have value as null.

so is there a way in which we can declare this constraint at the time of table creation.

Please suggest anything other than triggers.

Answer 1

If the purpose of x, y and z is to simplify some queries then rather than having x, y and z as columns on your table you could also consider using a view to do this e.g.

create view myview as
  select a, b, c,
  if (isnull(a), null, 1) as x,
  if (isnull(b), null, 1) as y,
  if (isnull(c), null, 1) as z
  from mytable;

and then base your other queries on this view instead of directly on the table.

Answer 2

The constraint you are looking for is the check constraint.

CREATE TABLE test
(
    a varchar(10),
    b varchar(10),
    c varchar(10),
    x integer,
    y integer,
    z integer,
    CONSTRAINT chk_X_Nulls CHECK ((a is null and x is null) or (a is not null and x = 1)),
    CONSTRAINT chk_Y_Nulls CHECK ((b is null and y is null) or (b is not null and y = 1)),
    CONSTRAINT chk_Z_Nulls CHECK ((c is null and z is null) or (c is not null and z = 1))
);

Unfortunately this isn't implemented in MySQL. There is an open bug report dating back to 2004 for this feature so don't expect to see it any time soon.

Others have answered that you can use triggers or views to achieve the desired result, and these are the correct answers for MySQL.

You can also partially constrain your data using some simple tricks:

• Set the data type of x, y, z to enum('1'). This will prevent values other than null and '1' from being inserted but won't ensure the values are correct.
• If a, b, c have a limited range of possible values you can create foreign key constraints to other tables and populate those tables with every possible value of a, b, c
• You can create an event to update x, y, z on a schedule (eg once every hour or once a day). The values for x, y, z can be corrected if they wrong.

You can see the check constraint in action with PostGreSQL here

If you need further advice please explain why triggers are inappropriate for your task.

Answer 3

MySQL doesn't handle CONSTRAINTS per-se, but you could implement a similar behavior using a TRIGGER on the BEFORE INSERT and BEFORE UPDATE events. You will, however, have to rely on some other table-level constrains (NOT NULL) to get it working, as per this other question on SO.

In your very specific case, it looks a lot like you would want to use the trigger to calculate the value of your x, y, z values in the trigger, rather than using it to prevent insertion of data with "improper" values - but your question does not make this point unambiguously clear, so it depends on what you really want.

Answer 4

Yes, you can use triggers for this.

From the Trigger syntax chapter:

If a BEFORE trigger fails, the operation on the corresponding row is not performed

Although the scenario you describe implies that the data is not normalized.

Answer 5

Besides constraints, you could achieve a similar result by not storing at all the x, y, z columns and using a view:

CREATE VIEW myView AS
SELECT
    a, b, c,
    ( a = a ) AS x,
    ( b = b ) AS y,
    ( c = c ) AS z
FROM myTable