Clever Geek Handbook

What's the best way to enforce single-level recursion in a SQL table?

Let's say you have a table for branches in your organization. Some of these are "main" branches, while others are satellite offices that are being rolled out into the mainstream. Apart from this difference, which only affects a few elements in the system, branches are peer-to-peer and have the same attributes (address, etc.). One way to model it is in a table, for example:

CREATE TABLE Branch (
    branch_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    branch_name VARCHAR(80) NOT NULL,
    street VARCHAR(80) NULL,
    city VARCHAR(30) NULL,
    state CHAR(2) NULL,
    zip CHAR(5) NULL,
    is_satellite_office BIT NOT NULL DEFAULT(0),
    satellite_to_branch_id INT NULL REFERENCES Branch(branch_id)
)

Where is_satellite_office

= 1 if this entry is a satellite for another branch, which satellite_to_branch_id

means which branch you are a satellite, if any.

It's easy enough to set a constraint on a table so that these two columns match any record:

CONSTRAINT [CK_Branch] CHECK 
  (
    (is_satellite_office = 0 AND satellite_to_branch_id IS NULL) 
    OR (is_satellite_office = 1 AND satellite_to_branch_id IS NOT NULL)
  )

However, I really want to ensure that this recursion is only level one ... that is, if I point to a branch as my parent, it should not have a parent itself, and its value for is_satellite_office

should be 0. By -otherwise, I really don't want a fully recursive tree structure, I just want to restrict it to one parent / child relationship. This is how I'm going to write the code, and if there is a way to provide it in a database that won't work like complete crap, I'd like to.

Any ideas? I'm working on MSSQL 2005, but generic (non-vendor specific) solutions are preferred. And no triggers are required unless there is another way to do it.

EDIT: To be clear, satellite_to_branch_id

is a recursive pointer to another record in the same Branch table. I know I could delete is_satellite_office BIT

and rely on IsNull(satellite_to_branch_id)

to give me the same information, but I find it clearer to be explicit, and furthermore, that is not the point of the question. I'm really looking for a clean way to limit SQL to prevent recursion-depth greater than 1.

+1


source to share


4 answers


You can bind a check constraint to the return value of the UDF. Create a UDF that takes the columns used as inputs and then check your desired state with a selection in the UDF.



+1


source


It seems to me like a business constraint that is difficult to enforce at the data definition level. I don't believe relational algebra has any support for defining a limit for the depth of native links.



+1


source


Are you not allowed to reference a stored procedure in your constraint? You can do it in PostgreSQL, so I would be surprised if it wasn't there in 2005.

+1


source


How about this slightly different structure?

CREATE TABLE Branch (
    branch_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    branch_name VARCHAR(80) NOT NULL,
    street VARCHAR(80) NULL,
    city VARCHAR(30) NULL,
    state CHAR(2) NULL,
    zip CHAR(5) NULL,
    parent_id int NULL
)

PARENT_ID will simply point to the BRANCH_ID of another record in the same table. If it is null, then you know it has no parents.

Then, to get one level of recursion, you can simply join the table to yourself once, like this:

SELECT
  PARENT.BRANCH_NAME AS PARENT_BRANCH
 ,CHILD.BRANCH_NAME AS CHILD_BRANCH
FROM
  BRANCH PARENT
 ,BRANCH CHILD
WHERE CHILD.PARENT_ID PARENT.BRANCH_ID

If you want to apply one level of depth in your tree, create an on-insert / update trigger that will throw an exception if this query returns anything.

SELECT *
FROM
  BRANCH B1
 ,BRANCH B2
 ,BRANCH B3
WHERE B1.PARENT_ID = :NEW.NEW_PARENT_ID
  AND B2.PARENT_ID = B1.BRANCH_ID
  AND B2.PARENT_ID = B3.BRANCH_ID;
0


source






More articles:


All Articles