Constraint In Mysql Table?

I have a table named Groups with primary key = Pkey. In Group there is a recursive association Parent_group references Pkey . I defined a Parent_Group as foreign key in relation Gr

Solution 1:

There are a few things about the two constraints you wish to impose:

1. New inserted row can not have NULL value for Parent_group column.

   • You can impose a NOT NULL constraint on a column only if it contains all non-null values. You need a null value in this column for the root node.
   • For this, you can use the CHECK constraint. Read more about the CHECK CONSTRAINT here.
   • You can put

CHECK ((peky= AND parent_group IS NULL) OR (peky!= AND parent_group IS NOT NULL))

This will allow a NULL value only for the root node and will enforce a NOT NULL value for every other row in the table.

2. Add a constrain so that RootGroup row can't be deleted.

   • That you have already defined a foreign key between parent_group and pkey, the database will automatically enforce referential integrity and forbid the root node (or for that matter any parent node) from being deleted. The database will return an error if a DELETE is attempted on any parent or root node.

3. For the point mentioned in the EDIT section, you can put a simple check constraint on the table like CHECK (parent_group != pkey). This should do the job for you.

Read about how to define foreign key constraints and how to use them to enforce referential integrity. Also, go through the link I have posted above or here before you apply these suggestions.