It past trick is the one examining one to an excellent NodeId can also be take part in a romance since a daddy

I’ve the 2 dining tables below, tips enforce constaint you to Nodes having NodeTypeId 2 (CannotBeParent) can not be a parent rather than bring about?

• Edited because of the gao.seng Wednesday,

Responses

• PM

I would begin by incorporating a flag to help you desk [NodeTypes], to indicate in case the node type of can participate while the moms and dad for the a love.

See that I am also incorporating an alternative limitation by the (NodeTypeId, CanBeParent). This is exactly redundant, but it enables us to later on use a different secret leading to it.

The next phase would be to create it column in order to table [Nodes], to make certain the partners (NodeTypeId, CanBeParent) exists into the dining table [NodeTypes]. We shall include a calculated continued line (ParentNodeId_CanBeParent), which have well worth equal step 1, to force [ParentNodeId] to become listed on because the mother or father automatically, and you can site dining table [Nodes] with the couple (ParentNodeId, ParentNodeId_CanBePArent).

It might be easier in the event that been able to have fun with a select declaration in to the a restriction, or becoming able to do assertions (take a look at restriction at the database top), however, T-SQL does not have ones possess as of today.

• 6:58 PM

All the answers

So how is Alex Kuznetsov when you need him? Alex attended up with constraintss in order to enforce organization legislation most some body do only thought would be you can easily having triggers.

However, he usually hits one adding an enthusiastic auxillary line. Maybe you will offer even more record about what you are looking to get to? That’ll to offer motivation to a few services.

In the event that Exists (See Most readily useful 1 step 1 Away from NodeTypes Interior Register Nodes Toward Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ‘CanBeParent’) Begin Place = 1 End More Start Set = 0 End Go back Avoid;

, NodeType varchar(255) maybe not null ) would dining table Nodes (NodeId int limitation PK__Nodes_NodeId Number one Key , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId records NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId sources Nodes (NodeId) Evaluate (dbo.CanBeParent(FK_ParentNodeid) = 1) )

insert Nodes values (1,1,1) <-- this will pass, because it has a parent 1 insert Nodes values (2,2,1) <-- this will pass, because it has a parent 1 which can be a parent insert Nodes values (3,2,2) <-- this will fail, the parent nodeId=2, is of NodeTypeId 2, which cant be a parent

• Suggested since answer by the sdsuser Wednesday,

We see Alex a lot on the SQLBlog I am able to look better around, nevertheless was right the greater I believe regarding it consult the fresh nearer I get to help you a simple solution. It is kind of instance that have a customer with a designated transformation rep and now have all members of the fresh new reps strings regarding demand each other down and up, although customer is more such a virtual staff member but can’t create individuals. In this instance a great workstation is also strings together with her a couple of products from a residential area pool including cams and you will microphones, nevertheless workstation by itself can not be a grandfather product. I think the customer-sales person dining table framework get implement right here, I will comment the program structure document to find out if you to is practical.

• Proposed as the respond to because of the sdsuser Wednesday,
• Unproposed while the respond to by sdsuser Wednesday,

• PM

Significantly less invisible because the a cause, it could really works. I’m able to observe nHibernate wants it the next day, and my manager.

• Edited by Kalman Toth Saturday,

I’d start by including a banner in order to desk [NodeTypes], to indicate if for example the node method of can participate because moms and dad in a relationship.

Note that I am also including an alternate restriction of the (NodeTypeId, CanBeParent). This might be redundant, it enables me to after use a different secret directing so you can they.

The next thing would be to put so it column in order to dining table [Nodes], to make certain your couple (NodeTypeId, CanBeParent) exists in the dining table [NodeTypes]. We’ll include a determined continuous column (ParentNodeId_CanBeParent), with worthy of equivalent step 1, to force [ParentNodeId] to become listed on given that mother automatically, and reference table [Nodes] using the couples (ParentNodeId, ParentNodeId_CanBePArent).

It hongkongcupid-promotiecodes will be easier if the been able to use a select declaration in to the a limitation, or becoming capable do assertions (check constraint in the database top), but T-SQL lacks of these provides as of today.

• 6:58 PM

There is no such thing as a “node_type_id” there can be a “_type” or a “_id” in a correct data model but not a weird hybrid. An identifier is for entities (emp_id) a type encoding a scalar value (blood_type).

I avoid using meta tags such as for instance “pk_” or “fk_” in data feature identity. The reason for a data function name’s to tell you what it is of the its nature and not how it are used in this particular table.

By using over-measurements of sequence such as this, their desk usually complete having trash. Good coders use records if group of viewpoints are higher or volition. Such as for example, your order detail UPC you’ll resource the newest Directory dining table in order to enforce a rule we just take purchases to possess gifts inside the stock.

If the put is smaller than average secure, after that we have fun with a check (x In the (..)) constants. Particularly, “sex_password TINYINT Standard 0 Maybe not NULL Look at (sex_code Within the (0, step 1, 2, 9)) — iso-5218

Would Table Nodes (node_id INTEGER Maybe not NULL Number one Secret, node_form of INTEGER Not NULL References Node_Sizes (node_type), parent_node_id INTEGER Records Nodes (node_id));

That which you are creating are an adjacency number design. It mimics low-relational tip chains within the SQL. It includes each other organizations (nodes) plus the steps (relationship). But a correct cannot be both (Chen? E-R modeling? Freshman database class at school?). Therefore while this is however an incorrect research design, you will have composed it

Manage Dining table Forest_and_Nodes (node_id INTEGER Not NULL First Key, node_sort of CHAR(1) Default ‘N’ Perhaps not NULL Examine (node_input (‘P’, ‘N’), — P= You’ll be able to Father or mother, N= Non-Mother mother or father_node_id INTEGER Records Tree_and_Nodes (node_id), Glance at (Circumstances Whenever node_type of = ‘N’ And you will father or mother_node_id Isn’t NULL Following ‘F’ More ‘T’ Prevent = ‘T’) );

–CELKO– Courses in the Celko Collection to possess Morgan-Kaufmann Publishing: Analytics and you will OLAP from inside the SQL / Study and Databases: Axioms used Study / Measurements and you may Standards into the SQL SQL getting Sming Build / SQL Puzzles and you will Answers / Convinced when you look at the Set / Trees and Hierarchies for the SQL