CODEWITHSUNDEEP
mysqlsqldatabase
rabbitfufu
rabbitfufu

Reputation: 13

Is it possible to enforce a constraint in mysql that considers the specific value of a column in another table?

I have a base table that represents hierarchial data using the nested sets model.

CREATE TABLE trees (
    id INT NOT NULL AUTO_INCREMENT,
    rootId INT DEFAULT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(rootId) REFERENCES trees(id)
);

So this table can contain multiple, separate trees, where rootId points to the top node of each tree.

Now I would like to add a new table to extend trees with a subtype. For example:

CREATE TABLE happyTrees (
    treeId INT NOT NULL,
    PRIMARY KEY(treeId),
    FOREIGN KEY(treeId) REFERENCES trees(id)
);

Rows inserted into happyTrees should point only to root level nodes in trees -- so rows in trees that have a rootId of null.

I can enforce this at the application level, but I am wondering: Is there a more elegant way to enforce this constraint in sql? So that it is only possible to insert a treeId into happyTrees if the corresponding rootId in trees is null?

Upvotes: 1

Views: 92

Answers (2)

Tony
Tony

Reputation: 10357

I would think about the design in a different way.

Either put the attributes in the trees table (a field is_happy) or create a separate tree_attributes table that references the tree structure.

Because ultimately a "happy" tree structure is still a tree structure.

Although I appreciate this does not create a database enforced constraint when inserting rows. And while I prefer database models that prevent bad data from being inserted in the first place, sometimes simplicity is better.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The following works for you specific situation:

CREATE TABLE trees (
    id INT NOT NULL AUTO_INCREMENT,
    rootId INT DEFAULT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (rootId, id),  -- redundant, but needed for foreign key relationship
    FOREIGN KEY(rootId) REFERENCES trees(id)
);

CREATE TABLE happyTrees (
    treeId INT NOT NULL,
    _rootId INT NULL, -- actually, ALWAYS NULL
    PRIMARY KEY(treeId),
    FOREIGN KEY(_rootId, treeId) REFERENCES trees(rootId, id);
);

This is adding a dummy column which you want to always be NULL. It can then reference the other table, guaranteeing that happy trees only points to the root.

Whether this is more elegant than doing the work in the application (or, better yet in my opinion, a stored procedure), is a matter of aesthetic tastes. It is more clever, though ;)

Upvotes: 0

Related Questions