Which last trick is the one examining you to an excellent NodeId can take part in a romance as the a daddy

Which last trick is the one examining you to an excellent NodeId can take part in a romance as the a daddy

You will find the 2 dining tables lower than, ideas on how to enforce constaint you to definitely Nodes with NodeTypeId 2 (CannotBeParent) can not be a dad versus bring about?

Solutions

I would personally start by including a flag to help you table [NodeTypes], to point if the node sorts of can engage since the father or mother when you look at the a romance.

Note that I am also including yet another limitation by the (NodeTypeId, CanBeParent). That is redundant, nonetheless it will allow us to after explore a foreign key directing to it.

The next step will be to incorporate this line also to dining table [Nodes], to make certain that the few (NodeTypeId, CanBeParent) can be acquired for the table [NodeTypes]. We shall also add a computed carried on line (ParentNodeId_CanBeParent), having worth equivalent step 1, to force [ParentNodeId] to become listed on while the mother or father automagically, and you may reference desk [Nodes] using the few (ParentNodeId, ParentNodeId_CanBePArent).

It might be convenient when the been able to play with a select report to the a check limitation, or being capable carry out assertions (evaluate constraint at databases level), but T-SQL lacks of these has to date.

Most of the responses

So where are Alex Kuznetsov when you need your? Alex came up with constraintss in order to enforce providers laws really people create simply think might be it is possible to having leads to.

But then, the guy will achieves one to by adding a keen auxillary line. Perchance you offers some more background on what you are seeking go? Which could supply determination to some solution.

In the event the Can be obtained (Look for Top step one step 1 From NodeTypes Internal Sign-up Nodes Into the Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‘CanBeParent’) Initiate Set = step one End More Begin Put = 0 End Come back Stop;

, NodeType varchar(255) maybe not null ) carry out dining table Nodes (NodeId int limitation PK__Nodes_NodeId Top Trick , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId records Nodes (NodeId) Check (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

We discover Alex much into SQLBlog I’m able to dig higher indeed there, but you is best the more I believe about it consult the brand new better I get to a remedy. It’s type of like which have a customers which have an assigned transformation rep while having most of the members of the new representatives chain regarding demand one another down and up, but the customer is much more such as for instance a virtual staff member but are unable to perform someone. In such a case a good workstation is strings along with her a collection of products off a residential district pond such adult cams and microphones, however the workstation by itself can’t be a parent device. In my opinion the client-sales representative dining table structure may use here, I could remark the program framework file to find out if that makes sense.

A lot less undetectable since a trigger, it might functions. I can see how nHibernate enjoys it tomorrow, and you may my personal movie director.

I would start by adding a flag to table [NodeTypes], to indicate when your node kind of can take part once the moms and dad in the a relationship.

Observe that And i am adding another restriction of the (NodeTypeId, CanBeParent). This is redundant, nevertheless enables me to afterwards have fun with a different key directing to they.

The next thing would be to include this line and dining table [Nodes], to ensure the partners (NodeTypeId, CanBeParent) can be found from inside the table [NodeTypes]. We’ll include a calculated persisted column (ParentNodeId_CanBeParent), which have well worth equivalent step one, to force [ParentNodeId] to join given that father or mother by default, and you may resource desk [Nodes] with the couples (ParentNodeId, ParentNodeId_CanBePArent).

It might be simpler when the been able to fool around with a choose report in to the a check constraint, or being in a position to would assertions (evaluate restriction within databases height), however, T-SQL does not have ones enjoys currently.

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 avoid using meta tags eg “pk_” or “fk_” during the investigation feature name. The objective of a document function name is to inform us the goals from the their character and never the way it try being used in this particular dining table.

If you use more than-measurements of string such as this, your own table commonly fill with trash. A great programmers explore recommendations in the event that band of viewpoints was large or volition. For example, an order outline UPC you will site the fresh Catalog dining table so you’re able to enforce a tip that we only take commands getting presents inside the inventory.

In the event that set are smaller than average steady, next i fool around with a (x Inside the (..)) constants. Such as for instance, “sex_password TINYINT Default 0 Not NULL Check (sex_password From inside the (0, step 1, dos, 9)) — iso-5218

Manage Dining table Nodes (node_id INTEGER Not NULL First Trick, node_style of INTEGER Not NULL Recommendations Node_Brands (node_type), parent_node_id INTEGER Records Nodes (node_id));

Everything you have created try a keen adjacency list design. They imitates low-relational pointer stores within the SQL. It includes one another agencies (nodes) as well as the steps (relationship). But a proper can’t be one another (Chen? https://datingranking.net/nl/hater-overzicht/ E-R acting? Freshman databases classification at school?). Thus although this is still an invalid analysis design, you will have composed it as

Would Table Tree_and_Nodes (node_id INTEGER Perhaps not NULL Number one Key, node_particular CHAR(1) Default ‘N’ Not NULL Check (node_type in (‘P’, ‘N’), — P= You can easily Mother, N= Non-Mother or father father or mother_node_id INTEGER References Tree_and_Nodes (node_id), Examine (Circumstances When node_types of = ‘N’ And you may father or mother_node_id Is not NULL After that ‘F’ Else ‘T’ Prevent = ‘T’) );

–CELKO– Instructions inside Celko Collection getting Morgan-Kaufmann Posting: Analytics and you will OLAP from inside the SQL / Research and you can Databases: Basics in practice Studies / Specifications and you may Criteria inside the SQL SQL to possess Sming Style / SQL Puzzles and you will Responses / Thinking within the Sets / Trees and you will Hierarchies for the SQL

دیدگاهتان را بنویسید