This past trick is certainly one checking that a great NodeId normally be involved in a romance while the a dad

This past trick is certainly one checking that a great NodeId normally be involved in a romance while the a dad

We have the 2 tables less than, how to demand constaint you to Nodes that have NodeTypeId dos (CannotBeParent) can’t be a father in place of end in?

Solutions

I’d begin by including a banner to desk [NodeTypes], to point if your node sort of normally engage as the parent from inside the a romance.

Observe that And i am incorporating another restriction of the (NodeTypeId, CanBeParent). This can be redundant, it enable us to later use a different key directing so you’re able to it.

The next phase is to put which line and also to dining table [Nodes], to be certain that the couple (NodeTypeId, CanBeParent) can be found within the dining table [NodeTypes]. We shall also add a determined continued line (ParentNodeId_CanBeParent), which have well worth equivalent step 1, to make [ParentNodeId] to participate because moms and dad automagically, and you can site dining table [Nodes] utilising the couple (ParentNodeId, ParentNodeId_CanBePArent).

It would be easier if was able to play with a select report inside a check limitation, or being capable do assertions (check constraint at the database peak), but T-SQL lacks of them has as of today.

All of the replies

So how is actually Alex Kuznetsov when you really need him? Alex attended up with constraintss to help you impose company laws extremely somebody create merely consider would be you’ll be able to with produces.

But then, he commonly hits that by the addition of a keen auxillary column. Maybe you will offer some more records about what you are seeking to achieve? That will to give determination to some solution.

If Is obtainable (Select Ideal step 1 1 Off NodeTypes Internal Sign up Nodes To your Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‘CanBeParent’) Start Lay = step one Stop More Begin Place = 0 Prevent Get back End;

, NodeType varchar(255) maybe not null ) do table Nodes (NodeId int constraint PK__Nodes_NodeId Primary Key , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId records Nodes (NodeId) Have a 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

I see Alex a great deal into the SQLBlog I will enjoy higher indeed there, however is actually correct the greater number of I believe regarding it demand the nearer I get to a simple solution. It’s style of including which have a customers which have an allocated transformation associate and have most of the people in the reps strings out of order one another top to bottom, however the customer is far more particularly a virtual employee however, cannot manage people. In this situation a beneficial workstation can strings along with her a set of products away from a residential area pool eg cams and microphones, however the workstation alone can not be a pops device. I think the customer-sales rep table structure will get incorporate here, I am able to feedback the software program construction file to find out if you to makes sense.

Not as invisible since a cause, it might performs. I’m able to see how nHibernate enjoys it tomorrow, and you will my director.

I would begin by adding a banner to table [NodeTypes], to point if the node kind of can engage because mother from inside the a relationship.

See that And i am including a different restriction from the (NodeTypeId, CanBeParent). This will be redundant, nevertheless allows us to later explore a foreign secret leading so you’re able to it.

The next thing should be to put that it column and to desk [Nodes], to be certain that the few (NodeTypeId, CanBeParent) can be found from inside the table [NodeTypes]. We’re going to also add a determined carried on column (ParentNodeId_CanBeParent), having really worth equivalent step 1, to make [ParentNodeId] to join because mother automagically, and you can site table [Nodes] utilizing the couple (ParentNodeId, ParentNodeId_CanBePArent).

It could be easier in the event that managed to play with a choose report to the a check limitation, or becoming able to manage assertions (glance at limitation in the databases height), but T-SQL lacks of those have to date.

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_” inside the investigation function identity. The reason for a document ability name is to inform us what it is of the its character and not how it try used in this desk.

If you are using more than-size of string similar to this, your own table usually fill with rubbish. Good programmers fool around with references in the event that selection of viewpoints is large otherwise volition. Such, your order outline UPC might source brand new List table so you can demand a tip we only take orders to own gift suggestions when you look at the stock.

When the set try smaller than average steady, after that we play with a (x In (..)) constants. For example, “sex_code TINYINT Standard 0 Perhaps not NULL Look at (sex_password Within the (0, step 1, dos, 9)) — iso-5218

Create Dining table Nodes (node_id INTEGER Perhaps not NULL No. 1 Key, node_type INTEGER Maybe not NULL Recommendations Node_Items (node_type), parent_node_id INTEGER Recommendations Nodes (node_id));

That which you have created was an adjacency checklist design. They mimics non-relational tip organizations within the SQL. It includes each other agencies (nodes) together with steps (relationship). However, a proper cannot be both (Chen? E-Roentgen acting? Freshman databases classification at school?). So although this is however an invalid research design, you should have composed it

Perform Table Forest_and_Nodes (node_id INTEGER Maybe not NULL No. 1 Key, node_kind of CHAR(1) Standard ‘N’ Perhaps not NULL Examine (node_enter in (‘P’, ‘N’), — P= Possible Mother or father, N= Non-Mother or father parent_node_id INTEGER Records Forest_and_Nodes (node_id), Evaluate (Case When node_type = ‘N’ And father or mother_node_id Isn’t NULL Following ‘F’ Otherwise ‘T’ Prevent = ‘T’) );

–CELKO– Courses when you look at the Celko Collection to have Morgan-Kaufmann Publishing: Analytics and you may OLAP during the SQL / Studies and Database: Basics used Data / Measurements and you can Conditions inside the SQL SQL to own Sming Build / SQL Puzzles and you may Answers / Considering during the Set / datingranking.net/amor-en-linea-review/ Woods and you can Hierarchies within the SQL

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *