It last trick is one checking you to definitely an excellent NodeId can be participate in a relationship since a daddy

It last trick is one checking you to definitely an excellent NodeId can be participate in a relationship since a daddy

I have both dining tables lower than, just how to enforce constaint one Nodes with NodeTypeId 2 (CannotBeParent) can not be a father instead of result in?

Answers

I would personally start by including a flag to help you desk [NodeTypes], to suggest in case your node sorts of normally engage given that mother or father for the a romance.

See that I am also incorporating a different limitation by (NodeTypeId, CanBeParent). This might be redundant, but it enable me to after explore a different secret pointing so you're able to they.

The next phase is to include this column and also to dining https://datingranking.net/nl/flirt-overzicht/ table [Nodes], to make certain that pair (NodeTypeId, CanBeParent) is obtainable when you look at the desk [NodeTypes]. We shall also add a computed persisted line (ParentNodeId_CanBeParent), which have worth equal step 1, to force [ParentNodeId] to participate while the father or mother by default, and source dining table [Nodes] making use of the couple (ParentNodeId, ParentNodeId_CanBePArent).

It could be smoother if managed to use a select report in to the a check limitation, or being in a position to create assertions (view restriction from the databases top), however, T-SQL does not have of these has actually to date.

Every feedback

Where are Alex Kuznetsov when you require him? Alex have come with constraintss in order to enforce organization statutes very anyone perform only imagine would be you'll having trigger.

Then again, he have a tendency to hits that adding a keen auxillary column. Perchance you will give a few more records on what you are seeking to achieve? Which could provide inspiration to a few provider.

If Exists (Select Greatest step one step 1 Away from NodeTypes Interior Sign-up Nodes With the Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = 'CanBeParent') Start Lay = 1 Stop Otherwise Initiate Place = 0 Prevent Come back Prevent;

, NodeType varchar(255) perhaps not null ) carry out table Nodes (NodeId int limitation PK__Nodes_NodeId Primary Trick , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId records NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId references 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

We read Alex a great deal on SQLBlog I'm able to dig better there, but you try correct the greater amount of I believe about any of it consult the closer I have so you're able to an answer. It’s form of like which have a customer that have an allocated transformation agent and also every members of the fresh new representatives chain off order each other up-and-down, nevertheless buyers is much more eg a virtual employee however, cannot do anyone. In this instance a good workstation can be strings together a couple of gizmos out-of a community pond particularly cams and you can microphones, nevertheless workstation in itself can not be a daddy device. In my opinion the consumer-sales representative table build may apply right here, I'm able to remark the software construction document to find out if one makes sense.

Not as hidden due to the fact a trigger, it could performs. I am able to find out how nHibernate enjoys they tomorrow, and you will my director.

I would begin by incorporating a flag so you're able to dining table [NodeTypes], to point if for example the node sort of normally participate since the parent during the a love.

See that And i am including another type of limitation from the (NodeTypeId, CanBeParent). It is redundant, nevertheless enables us to later fool around with a different key pointing so you're able to they.

The next step is to try to put it line also to dining table [Nodes], to make certain that the few (NodeTypeId, CanBeParent) is available from inside the desk [NodeTypes]. We are going to include a calculated proceeded column (ParentNodeId_CanBeParent), having well worth equivalent 1, to force [ParentNodeId] to become listed on since father or mother automatically, and you may reference dining table [Nodes] utilizing the couples (ParentNodeId, ParentNodeId_CanBePArent).

It would be much easier when the were able to explore a select statement inside a restriction, or being capable create assertions (glance at limitation at the databases peak), but T-SQL does not have ones features 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 instance “pk_” or “fk_” into the research function name. The reason for a document ability name's to share with united states the goals from the its nature rather than how it try being used in this particular desk.

If you utilize more than-size of sequence like this, the table commonly complete with rubbish. Good programmers use references if set of beliefs was higher or volition. Such, an order detail UPC might reference brand new Collection table in order to enforce a tip that we only take orders to have merchandise during the stock.

In the event the set is small and secure, after that we use a check (x In the (..)) constants. For example, “sex_password TINYINT Default 0 Not NULL Evaluate (sex_password Inside (0, step one, dos, 9)) — iso-5218

Do Dining table Nodes (node_id INTEGER Maybe not NULL Top Secret, node_kind of INTEGER Not NULL Records Node_Designs (node_type), parent_node_id INTEGER Sources Nodes (node_id));

Everything are creating was an adjacency list design. It mimics low-relational tip chains when you look at the SQL. It has both organizations (nodes) and the steps (relationship). But a proper can't be one another (Chen? E-Roentgen acting? Freshman databases class at school?). Very while this is nonetheless an invalid investigation model, you have created it as

Do Table Tree_and_Nodes (node_id INTEGER Perhaps not NULL Primary Secret, node_sort of CHAR(1) Default 'N' Maybe not NULL Glance at (node_enter in ('P', 'N'), — P= You are able to Moms and dad, N= Non-Mother or father mother or father_node_id INTEGER Recommendations Tree_and_Nodes (node_id), Take a look at (Instance Whenever node_type of = 'N' And you will mother or father_node_id Isn’t NULL Up coming 'F' Otherwise 'T' End = 'T') );

–CELKO– Books in Celko Collection to have Morgan-Kaufmann Posting: Analytics and you will OLAP during the SQL / Studies and you will Databases: Axioms used Investigation / Dimensions and you may Standards in the SQL SQL for Sming Build / SQL Puzzles and Responses / Considering when you look at the Set / Woods and you will Hierarchies inside the SQL

כתיבת תגובה