I’ve the 2 tables lower than, how to enforce constaint one to Nodes which have NodeTypeId dos (CannotBeParent) cannot be a father in place of produce?

• Edited from the gao.seng Wednesday,

Answers

• PM

I’d start with including a banner in order to dining table [NodeTypes], to suggest in case the node types of is take part once the mother inside a relationship.

Notice that I am also including an alternative constraint from the (NodeTypeId, CanBeParent). This will be redundant, nevertheless enable me to after have fun with a different key leading in order to they.

The next step will be to put which line also to table [Nodes], to ensure that the pair (NodeTypeId, CanBeParent) is obtainable from inside the dining table [NodeTypes]. We are going to include a computed continuous column (ParentNodeId_CanBeParent), with worth equivalent 1, to make [ParentNodeId] to become listed on as the moms and dad automagically, and site table [Nodes] making use of the couples (ParentNodeId, ParentNodeId_CanBePArent).

It will be convenient in the event that been able to explore a select statement to the a check constraint, or becoming able to perform assertions (evaluate limitation at database top), but T-SQL does not have of them enjoys currently.

• 6:58 PM

All the feedback

So where are Alex Kuznetsov when you require your? Alex came with constraintss so you’re able to enforce team laws and regulations most people carry out just envision will be you are able to having causes.

But then, the guy often reaches one with the addition of an enthusiastic auxillary line. Maybe you deliver even more records about what you are looking to achieve? Which could to provide desire to a few service.

If Is obtainable (See Ideal step 1 step 1 Off NodeTypes Inner Join Nodes Into Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ‘CanBeParent’) Begin Set = step one Prevent More Initiate Lay = 0 Prevent Get back Avoid;

, NodeType varchar(255) perhaps not null ) do table Nodes (NodeId int constraint PK__Nodes_NodeId Primary Trick , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId references NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId recommendations 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 since address of the sdsuser Wednesday,

We comprehend Alex much towards the SQLBlog I will dig higher around, but you is right more I believe about it request the latest closer I get so you can a solution. It is sort of for example with a buyers having an assigned conversion process agent and just have all the members of the fresh reps chain of order both along, nevertheless the customer is more such as an online staff however, cannot do some one. In this situation an effective workstation can be strings together a couple of devices of a residential district pond including cams and you may microphones, nevertheless the workstation in itself can’t be a parent equipment. I believe the customer-sales agent desk structure get implement right here, I can remark the program structure clover dating document to find out if that is reasonable.

• Recommended because the respond to of the sdsuser Wednesday,
• Unproposed since respond to from the sdsuser Wednesday,

• PM

Never as invisible just like the a trigger, it may performs. I could find out how nHibernate enjoys they the next day, and you can my personal director.

• Edited by Kalman Toth Monday,

I would personally begin by adding a flag so you can dining table [NodeTypes], to indicate if for example the node type of can engage because the father or mother into the a relationship.

Note that I am also including another type of limitation from the (NodeTypeId, CanBeParent). This really is redundant, but it enables me to later on have fun with a foreign secret directing in order to it.

The next thing will be to add it column also to dining table [Nodes], to be sure that pair (NodeTypeId, CanBeParent) can be found in the desk [NodeTypes]. We’re going to also add a calculated continued column (ParentNodeId_CanBeParent), that have really worth equivalent step one, to force [ParentNodeId] to become listed on because the moms and dad automagically, and you may site table [Nodes] by using the couples (ParentNodeId, ParentNodeId_CanBePArent).

It would be convenient if been able to explore a select declaration into the a constraint, or becoming able to do assertions (look at constraint at database height), however, T-SQL lacks of them has as of today.

• 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 for example “pk_” or “fk_” from inside the research ability label. The reason for a document ability name’s to share with all of us what it is from the the characteristics rather than the way it are being used in this particular table.

By using over-measurements of string like this, your own dining table will complete having rubbish. A beneficial coders play with recommendations if group of values is large or volition. For example, your order detail UPC might site the newest Catalog table to impose a rule that we only take purchases having gift ideas for the inventory.

When the place was smaller than average stable, next i fool around with a (x For the (..)) constants. Such, “sex_code TINYINT Standard 0 Not NULL Evaluate (sex_code Into the (0, step one, dos, 9)) — iso-5218

Manage Desk Nodes (node_id INTEGER Not NULL Top Trick, node_types of INTEGER Perhaps not NULL Recommendations Node_Sizes (node_type), parent_node_id INTEGER References Nodes (node_id));

Everything you are creating is actually an enthusiastic adjacency checklist design. It mimics non-relational pointer chains inside SQL. It contains one another organizations (nodes) as well as the steps (relationship). But a correct can not be each other (Chen? E-Roentgen modeling? Freshman database group at school?). Therefore although this is however an invalid analysis design, you’ll have created it as

Would Dining table Tree_and_Nodes (node_id INTEGER Maybe not NULL Number 1 Trick, node_particular CHAR(1) Default ‘N’ Maybe not NULL Look at (node_enter in (‘P’, ‘N’), — P= You’ll be able to Mother, N= Non-Parent father or mother_node_id INTEGER References Forest_and_Nodes (node_id), Consider (Instance Whenever node_variety of = ‘N’ And you will parent_node_id Is not NULL Up coming ‘F’ Else ‘T’ End = ‘T’) );

–CELKO– Courses in the Celko Show to possess Morgan-Kaufmann Posting: Statistics and you can OLAP for the SQL / Investigation and you can Databases: Basics used Study / Dimensions and you will Criteria in SQL SQL to own Sming Build / SQL Puzzles and you may Solutions / Thinking into the Kits / Trees and you will Hierarchies inside SQL