title: Add check in SQL Server, based on a row value from a row connected by foreign key?
body: <p>I want to create the following 2 tables, in which the start date has to be before the end date:</p>
  
  <pre><code>CREATE TABLE ParentEntity 
  (
      ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,    
      StartDate date NOT NULL,    
      EndDate date NOT NULL, 
      Description varchar(255)
  ); 
  
  ALTER TABLE ParentEntity
    ADD CHECK (DATEDIFF(day,StartDate ,EndDate) &gt; 0)
  
  CREATE TABLE ChildEntity 
  ( 
      ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,    
      Parent int NOT NULL FOREIGN KEY REFERENCES ParentEntity (ID),    
      StartDate date NOT NULL,    
      EndDate date NOT NULL, 
      Description varchar(255)
  ); 
  
  ALTER TABLE ChildEntity 
    ADD CHECK (DATEDIFF(day,StartDate ,EndDate) &gt; 0)
  </code></pre>
  
  <p>Now I want to add this check: the <code>StartDate</code> and <code>EndDate</code> of each <code>ChildEntity</code> row must occur within the date interval between the <code>StartDate</code> and <code>EndDate</code> of the corresponding <code>ParentEntity</code> row.</p>
  
  <p>How can I make this check? I do not know how to refer to the row which is defined by means of foreign key.</p>