We have both dining tables below, how to demand constaint one Nodes having NodeTypeId dos (CannotBeParent) can not be a parent rather than trigger?

• Edited of the gao.seng Wednesday,

Answers

• PM

I would personally begin by including a flag in order to dining table [NodeTypes], to indicate if for example the node sorts of can be engage once the mother or father in a love.

Notice that And i am including another type of constraint because of the (NodeTypeId, CanBeParent). This is exactly redundant, but it allows us to later have fun with a different key leading so you can it.

The next step is to try to add so it line and desk [Nodes], to make certain the couples (NodeTypeId, CanBeParent) can be found during the dining table [NodeTypes]. We shall also add a determined continued line (ParentNodeId_CanBeParent), having worthy of equal step 1, to make [ParentNodeId] to join as the mother or father automagically, and you may site desk [Nodes] utilizing the partners (ParentNodeId, ParentNodeId_CanBePArent).

It will be convenient in the event the were able to explore a select statement to the a constraint, or being in a position to create assertions (see restriction at databases level), but T-SQL lacks of them has as of today.

• 6:58 PM

The feedback

So how is actually Alex Kuznetsov if you want your? Alex attended up with constraintss so you can enforce team statutes most individuals do only thought could well be you’ll that have leads to.

Then again, the guy commonly achieves that by adding a keen auxillary line. Perhaps you can give even more background about what you are trying to get to? Which will supply inspiration to some provider.

In the event the Is available (Look for Top 1 step 1 Of NodeTypes Interior Signup Nodes With the Nodes.NodeId = And you will Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you can NodeTypes.NodeType = ‘CanBeParent’) Begin Put = step 1 End Otherwise Start Set = 0 Avoid Go back Avoid;

, NodeType varchar(255) not null ) would desk Nodes (NodeId int constraint PK__Nodes_NodeId No. 1 Secret , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId references NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId references 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

• Proposed due to the fact respond to of the sdsuser Wednesday,

We understand Alex much with the SQLBlog I could search better indeed there, however you is correct the greater I believe about any of it request this new nearer I get in order to a simple solution. It’s kind of such as for example that have a consumer having an allocated conversion process agent and possess every people in this new representatives strings of order each other top to bottom, but the customers is far more for example an online staff however, can not perform individuals. In cases like this an excellent workstation can be chain along with her a set of products from a residential district kinkyadsprofiel pool eg webcams and microphones, although workstation in itself can’t be a daddy unit. In my opinion the consumer-salesperson dining table construction could possibly get implement here, I can feedback the software design document to find out if one to is reasonable.

• Proposed once the respond to of the sdsuser Wednesday,
• Unproposed as the answer because of the sdsuser Wednesday,

• PM

A lot less undetectable since a cause, it may functions. I could see how nHibernate wants it the next day, and my movie director.

• Modified of the Kalman Toth Friday,

I would begin by incorporating a banner so you can table [NodeTypes], to suggest if your node kind of normally engage as the moms and dad during the a love.

Note that I am also including an alternate constraint of the (NodeTypeId, CanBeParent). This really is redundant, it allows us to afterwards explore a foreign key directing so you can it.

The next step is to incorporate this column also to table [Nodes], to be certain that few (NodeTypeId, CanBeParent) can be obtained in desk [NodeTypes]. We will include a determined continuous column (ParentNodeId_CanBeParent), having value equivalent step 1, to force [ParentNodeId] to participate because father or mother by default, and you may site desk [Nodes] using the couples (ParentNodeId, ParentNodeId_CanBePArent).

It could be easier if the was able to explore a choose statement into the a check limitation, or being able to would assertions (consider constraint at databases height), however, T-SQL does not have of these possess to date.

• 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).

We never use meta data like “pk_” or “fk_” during the analysis function term. The goal of a document ability name’s to tell us the goals by the the characteristics rather than the way it was being used in this table.

If you utilize more than-measurements of string like this, your desk often complete that have rubbish. Good coders have fun with sources in the event that gang of viewpoints are higher or volition. Like, your order outline UPC you’ll resource brand new Directory dining table in order to demand a rule that we only take requests getting merchandise from inside the stock.

In the event the set try smaller than average steady, upcoming i use a (x Inside the (..)) constants. Such as, “sex_code TINYINT Default 0 Not NULL Have a look at (sex_code In the (0, 1, dos, 9)) — iso-5218

Perform Table Nodes (node_id INTEGER Not NULL First Secret, node_particular INTEGER Not NULL Recommendations Node_Designs (node_type), parent_node_id INTEGER References Nodes (node_id));

Everything you are creating is an adjacency record model. They imitates non-relational tip organizations from inside the SQL. It contains each other entities (nodes) additionally the hierarchy (relationship). But the correct can not be one another (Chen? E-R modeling? Freshman databases classification at school?). Thus although this is still an invalid analysis model, you’ll have composed it

Manage Dining table Tree_and_Nodes (node_id INTEGER Not NULL Top Trick, node_method of CHAR(1) Standard ‘N’ Not NULL View (node_type in (‘P’, ‘N’), — P= You can easily Parent, N= Non-Parent moms and dad_node_id INTEGER References Tree_and_Nodes (node_id), Examine (Case Whenever node_form of = ‘N’ And you may father or mother_node_id Isn’t NULL Upcoming ‘F’ More ‘T’ Stop = ‘T’) );

–CELKO– Courses inside Celko Series to own Morgan-Kaufmann Publishing: Statistics and you will OLAP in SQL / Data and Databases: Axioms used Study / Specifications and you can Requirements in the SQL SQL for Sming Concept / SQL Puzzles and you may Answers / Thinking within the Kits / Woods and Hierarchies into the SQL