We have the 2 dining tables lower than, how-to impose constaint one Nodes with NodeTypeId 2 (CannotBeParent) can’t be a father in place of produce?

• Modified because of the gao.seng Wednesday,

Solutions

• PM

I would begin by including a flag in order to table [NodeTypes], to point in case the node sort of is engage due to the fact father or mother in the a relationship.

See that I am also adding a unique restriction of the (NodeTypeId, CanBeParent). This really is redundant, but it will allow me to afterwards have fun with a foreign trick directing in order to it.

The next thing will be to create which line and to desk [Nodes], to be sure the couple (NodeTypeId, CanBeParent) exists in table [NodeTypes]. We will also add a determined continuing line (ParentNodeId_CanBeParent), having well worth equal 1, to make [ParentNodeId] to participate while the father or mother automagically, and you can source dining table [Nodes] using the pair (ParentNodeId, ParentNodeId_CanBePArent).

It will be much easier in the event the been able to explore a select statement in to the a limitation, or being in a position to create assertions (have a look at constraint in the databases top), however, T-SQL does not have of these has as of today.

• 6:58 PM

The answers

Where try Alex Kuznetsov if you want him? Alex came with constraintss to impose organization laws extremely some one perform only think is you can that have causes.

Then again, he often hits one to by the addition of an enthusiastic auxillary column. Perchance you can give a few more record on which you are looking to reach? Which will supply determination to some services.

In the event that Can be acquired (Select Finest step one step one Away from NodeTypes Interior Sign up Nodes Into the Nodes.NodeId = And you can Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ‘CanBeParent’) Initiate Place = step one Stop Otherwise Begin Put = 0 Prevent Go back End;

, NodeType varchar(255) maybe not null ) manage desk Nodes (NodeId int limitation PK__Nodes_NodeId Number one Key , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId recommendations Nodes (NodeId) Look at (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

• Advised once the address because of the sdsuser Wednesday,

I realize Alex a lot to your SQLBlog I’m able to enjoy deeper truth be told there, nevertheless is actually right the greater amount of In my opinion about any of it consult the newest closer I get to a remedy. It is type of such which have a consumer that have an allocated conversion process representative and also all people in the latest staff chain regarding order one another up-and-down, nevertheless consumer is far more instance an online staff member but are unable to create some body. In this instance an effective workstation can be chain together with her a collection of devices out-of a community pond including cams and you can microphones, but the workstation itself cannot be a daddy device. I think the client-sales person table design can get implement here, I could remark the software framework file to see if you to definitely is practical.

• Recommended as the address by the sdsuser Wednesday,
• Unproposed while the answer because of the sdsuser Wednesday,

• PM

Notably less undetectable because a cause, it could works. I could find out how nHibernate loves it the next day, and you may my movie director.

• Modified by the Kalman Toth Tuesday,

I would personally start with adding a banner so you’re able to dining table [NodeTypes], to suggest whether your node sorts of can be participate given that moms and dad inside the a relationship.

See that And i am adding a different sort of limitation from the (NodeTypeId, CanBeParent). This is redundant, however it enable us to after use a different trick directing in order to it.

The next step is to create it line also to dining table [Nodes], to make sure that partners (NodeTypeId, CanBeParent) is present during the table [NodeTypes]. We shall also add a calculated continuous line (ParentNodeId_CanBeParent), having well worth equal 1, to force [ParentNodeId] to participate due to the fact parent by default, and site dining table [Nodes] using the few (ParentNodeId, ParentNodeId_CanBePArent).

It could be easier in the event that was able to use a select statement in to the a check restriction, or becoming capable do assertions (evaluate constraint at database level), but T-SQL lacks of these has actually currently.

• 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 never use meta data instance “pk_” otherwise “fk_” within the research function identity. The goal of a document element name is to tell you what it is from the their nature and never how it is being used in this dining table.

If you are using more than-sized sequence like this, their table will fill with rubbish. An excellent programmers fool around with recommendations in the event the selection of beliefs is actually large or volition. Such as for instance, an order outline UPC you will reference brand new Inventory desk so you’re able to impose a rule we just take sales to own merchandise during the inventory.

If put try smaller than average secure, up coming we explore a (x From inside the (..)) constants. For example, “sex_password TINYINT Standard 0 Maybe not NULL Look at (sex_password In (0, step 1, 2, 9)) — iso-5218

Create Desk Nodes (node_id INTEGER Maybe not NULL First Secret, node_form of INTEGER Perhaps not NULL References Node_Products (node_type), parent_node_id INTEGER Records Nodes (node_id));

What you are creating try an enthusiastic adjacency number design. It imitates low-relational tip chains for the SQL. It includes both entities (nodes) as well as the hierarchy (relationship). However, a proper cannot be one another (Chen https://datingranking.net/nl/her-overzicht/? E-Roentgen modeling? Freshman databases group in school?). Thus while this is still an incorrect data model, you have composed it

Would Desk Forest_and_Nodes (node_id INTEGER Perhaps not NULL Number one Trick, node_types of CHAR(1) Standard ‘N’ Not NULL Evaluate (node_input (‘P’, ‘N’), — P= You’ll Moms and dad, N= Non-Parent parent_node_id INTEGER Recommendations Forest_and_Nodes (node_id), Check (Situation When node_sort of = ‘N’ And you may moms and dad_node_id Is not NULL Up coming ‘F’ Otherwise ‘T’ End = ‘T’) );

–CELKO– Instructions in the Celko Series to own Morgan-Kaufmann Publishing: Analytics and you can OLAP inside the SQL / Analysis and you can Databases: Rules in practice Data / Specifications and you can Conditions in the SQL SQL for Sming Design / SQL Puzzles and Solutions / Thought inside Kits / Woods and you will Hierarchies during the SQL