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.

guys thanks for responses, but please suggest any thing other than triggers, because I have read that we can put some constraints in create table.. — Sashi Kant, Dec 07 '11 at 13:56
Nope, you cannot. Unlike most other DBMS (Postgre, Oracle, SQL Server...) MySQL does not support check constraints. — Álvaro González, Nov 22 '12 at 15:19

score 7 · Accepted Answer · answered Dec 07 '11 at 13:42

7

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.

answered Dec 07 '11 at 13:42

mikej

65,295
17
152
131

Hey thanks for ur response, but in that table there are many places where the inserts take place, It is a huge application and we are having two different platforms... – Sashi Kant Dec 07 '11 at 13:47
Can you please elaborate the approach, or provide a reference of this this. – Sashi Kant Nov 23 '12 at 14:18
Hi. Which bit is unclear? The MySQL documentation on views is [here](http://dev.mysql.com/doc/refman/5.6/en/views.html). – mikej Nov 23 '12 at 16:15

score 4 · Answer 2 · answered Nov 18 '12 at 13:34

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.

score 1 · Answer 3 · edited May 23 '17 at 11:48

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.

score 1 · Answer 4 · answered Dec 07 '11 at 13:37

1

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.

answered Dec 07 '11 at 13:37

symcbean

47,736
6
59
94

score 1 · Answer 5 · answered Dec 07 '11 at 13:42

1

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

answered Dec 07 '11 at 13:42

ypercubeᵀᴹ

113,259
19
174
235

create table constraint in mysql

5 Answers5

Linked