Create table constraint in mysql

Question

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

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

For example, say, The values ​​stored in a are 2 , and x is the column depending on it. So x will have a value of 1 .

If the values ​​stored in a are null , then x will be null .

so there is a way that we can declare this constraint during table creation.

Please suggest something other than triggers.

Answer 1

If the goal of x , y and z is to simplify some queries, instead of having x , y and z as columns in your table, you might also consider using a view for this, like

 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, not directly on the table.

Answer 2

The restriction you are looking for is the validation restriction.

 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 is not implemented in MySQL . There is an open bug report for 2004 for this feature, so don't expect to see it anytime soon.

Others 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 limit your data with simple tricks:

• Set the data type x, y, z to enum('1') . This will prevent the insertion of values ​​other than null and '1' , but does not guarantee that the values ​​are correct.
• If a, b, c has a limited range of possible values, you can create foreign key constraints for other tables and populate these tables with any possible value of a, b, c
• You can create an event to update x, y, z on a schedule (for example, once an hour or once a day). Values ​​for x, y, z can be corrected if they are erroneous.

You can see the check constraint in action with PostGreSQL here

If you need further advice, explain why triggers are not suitable for your task.

Answer 3

MySQL does not handle CONSTRAINTS per-se, but you can implement similar behavior using TRIGGER on BEFORE INSERT and BEFORE UPDATE . However, you will have to rely on some other table-level restrictions ( NOT NULL ) to make it work like this other SO question .

In your particular case, it looks like you would like to use a trigger to calculate the value of your x, y, z values ​​in the trigger, and not to use it to prevent the insertion of data with "incorrect" values ​​- but your question does not do this unambiguously unambiguous, so it depends on what you really want.

Answer 4

Yes, you can use triggers for this.

The trigger syntax chapter:

If the BEFORE trigger does not work, the operation on the corresponding line is not performed

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

Answer 5

In addition to limitations, you can achieve a similar result when storing not in all columns x, y, z and using the view:

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