ddutils.java

derby database source code.good for you.

			//check whether it matched with existing self references.
			// If A self-referencing constraint exists with a delete rule of
			// SET NULL,  NO ACTION or RESTRICT. We can not add CASCADE
			// relationship with another table.
				
			if(currentSelfRefValue !=  -1)
			{
				if(refActionType == StatementType.RA_CASCADE && 
				   currentSelfRefValue != StatementType.RA_CASCADE) 
				{
					throw generateError(SQLState.LANG_DELETE_RULE_CANT_BE_CASCADE_ESELF,  myConstraintName);
					
				}

			}

			
			//check for the cases with existing relationships to the
			//referenced table
			if(rAction != null)
			{
				checkForMultiplePathInvalidCases(rAction.intValue(),
												  refActionType,
												  myConstraintName,currentRefTableName);
			}

			
			//mark the current connect to the reference table to identify the cycle.
			if(refActionType != StatementType.RA_CASCADE)
			{
				prevNotCascade = true;
			}
			
			/*
			** cycle string is used to keep track of the referential actions of 
			** the nodes we visited, this is required to make sure that in case
			** of cycles , all the nodes in the cycle have same type of
			** referential action.
			**/
			cycleString = cycleString.append(refActionType);
		}


		boolean passedInPrevNotCascade = prevNotCascade;

		//delete connection is broken for if we see ON DELET SET NULL link
		// one level deeper than the table we are adding the foreing key
		//Where as to check for cycles we need to go for more level also;
        // To check cases like CASCADE CASCADE SET NULL cycle is not valid. 
		//Following variable is used make the distinction.
		boolean multiPathCheck = true;

		// check for cases where the new connection we are forming to the 
		// reference table could create invalid any cycles or mutiple paths 
		// with the delete-connections the  referencing table might have already.
		ConstraintDescriptorList refCDL = dd.getConstraintDescriptors(refTd);
		int refCDLSize = refCDL.size();
		for (int index = 0; index < refCDLSize; index++)
		{
			ConstraintDescriptor cd = refCDL.elementAt(index);

			if ((cd instanceof ForeignKeyConstraintDescriptor))
			{
				ForeignKeyConstraintDescriptor fkcd = (ForeignKeyConstraintDescriptor) cd;
				String constraintName = fkcd.getConstraintName();
				int raDeleteRule = fkcd.getRaDeleteRule();
				int raUpdateRule = fkcd.getRaUpdateRule();
				
				ReferencedKeyConstraintDescriptor refcd =
					fkcd.getReferencedConstraint(); 
				TableDescriptor nextRefTd = refcd.getTableDescriptor();

				//if we are not cascading, check  whether the link before
				//this was cascade or not. If we travel through  two NON CASCADE ACTION
				//links then the delete connection is broken(only a delete can have further
				//referential effects)
				if(raDeleteRule != StatementType.RA_CASCADE)
				{
					if(prevNotCascade)
					{
						prevNotCascade = passedInPrevNotCascade;
						continue;
					}
					else
					{
						prevNotCascade = true;
						multiPathCheck = false;
					}

				}

				//check whether the current link is a self referencing one
				boolean isSelfRefLink = fkcd.isSelfReferencingFK();
				
				//check for this is non self referencing cycles case
				//In cases of cycle, whole cycle should have the same refAction
				// value. Other wise we should throw an exception
				cycleString = cycleString.append(raDeleteRule);
				boolean isFormingCycle = (nextRefTd.getUUID().equals(actualTd.getUUID()));
				if(isFormingCycle)
				{
					//make sure that all the nodes in the cycle have the same
					//referential action  value, otherwise we should throw an error. 
					for(int i = 0 ; i < cycleString.length(); i++)
					{
						int otherRefAction = Character.getNumericValue(cycleString.charAt(i));
						if(otherRefAction != refActionType)
						{
							//cases where one of the existing relation ships in
							//the cycle is not cascade , so we can not have
							// cascade relation ship.
							if(otherRefAction != StatementType.RA_CASCADE)
							{
								throw generateError(SQLState.LANG_DELETE_RULE_CANT_BE_CASCADE_ECYCLE, myConstraintName);
							}
							else
							{
								//possibly all the other nodes in the cycle has
								//cascade relationsship , we can not add a non
								//cascade relation ship.
								throw
									generateError(SQLState.LANG_CANT_BE_DEPENDENT_ECYCLE, 
												  myConstraintName, currentRefTableName);
							}
						}
					}
				}	


				

				String nextRefTableName =  nextRefTd.getSchemaName() + "." + nextRefTd.getName();
				rAction = ((Integer)ech.get(nextRefTableName));
				if(rAction != null)
				{
					/*
					** If the table name has entry in the hash table means, there
					** is already  a path to this table exists from the table
					** the new foreign key relation ship is being formed.
					** Note: refValue in the hash table is how the table we are
					** adding the new relationsship is going to affected not
					** current path refvalue.
					**/
					if(!isSelfRefLink && multiPathCheck)
						checkForMultiplePathInvalidCases(rAction.intValue(),
														 refActionType, 
														 myConstraintName,currentRefTableName);

				}else
				{
					rAction = ((Integer)dch.get(nextRefTableName));
					if(rAction == null)
					{
						if(multiPathCheck)
							dch.put(nextRefTableName, (new Integer(refActionType)));
						if(!isSelfRefLink)
						{
							validateDeleteConnection(dd, actualTd,  nextRefTd,
												 refActionType, dch, ech, false,
												 myConstraintName,prevNotCascade,
												 cycleString, currentRefTableName, 
												 isSelfReferencingFk, currentSelfRefValue); 
						}
					}
				}
				prevNotCascade = passedInPrevNotCascade;
				//removes the char added for the current call
				cycleString.setLength(cycleString.length() -1);
				
			}
		}
	}


	/*
	**Check whether the mulitple path case is valid or not following
	** cases are invalid:
	** case 1: The relationship causes the table to be delete-connected to
	** the indicated table through multiple relationships and the
	** delete rule of the existing relationship is SET NULL. 
	** case 2: The relationship would cause the table to be
	** delete-connected to the same table through multiple
	** relationships and such relationships must have the same 
	** delete rule (NO ACTION, RESTRICT or CASCADE). 
	** case 3: The relationship would cause another table to be
	** delete-connected to the same table through multiple paths
	** with different delete rules or with delete rule equal to SET NULL. 
	**/

	private static void checkForMultiplePathInvalidCases(int currentRefAction,
														  int refActionType,
														  String myConstraintName,
														  String currentRefTableName)
		throws StandardException
	{

		//All the relation ship referring to a table should have the
		//same refaction except incase of SET NULL
		if(currentRefAction != refActionType)
		{

			//If there is a SET NULL relation ship we can not have any
			// other relation ship with it.
			if(currentRefAction == StatementType.RA_SETNULL)
				throw generateError(SQLState.LANG_CANT_BE_DEPENDENT_MPATH,
									myConstraintName, currentRefTableName);
			else
				//This error say what the delete rule must be for the
				// foreign key be valid 
				throw generateError(SQLState.LANG_DELETE_RULE_MUSTBE_MPATH,
									myConstraintName, currentRefAction);

		}else
		{
			//more than one  ON DELET SET NULL to the same table is not allowed
			if(currentRefAction == StatementType.RA_SETNULL &&
			   refActionType == StatementType.RA_SETNULL)
			{
				throw		
					generateError(SQLState.LANG_CANT_BE_DEPENDENT_MPATH,
								  myConstraintName, currentRefTableName);
			}
		}
	}



    /*
	** Check whether the delete rule of FOREIGN KEY  must not be CASCADE because
	** the  new relationship would cause another table to be delete-connected to
	** the same table through multiple paths with different delete rules or with 
	** delete rule equal to SET NULL. 
	**
	** For example :
	**                      t1
    **  		 CASCADE   /  \  CASCADE
    **                    /    \ 
	**                  t2      t3
    **                   \      /   
    **          SET NULL  \    /  CASCADE (Can we add this one ? NO)
 	**			          \   /
	                       \t4/
	**					
    **   existing links:
	**   t2 references t1   ON DELETE CASCADE  (fkey1)
	**   t3 references t1   ON DELETE CASCADE  (fkey2)
	**   t2 reference  t4   ON DELETE SET NULL (fkey3)
	**   Now if if try to add a new link i.e
	**   t4 references t3   ON DELETE SET NULL  (fkey4)
	**   Say if we add it,  then if we execute 'delete from t1' 
	**   Because of referential actions , we will try to delete a row through
	**   one path and tries to update  through another path. 
	**   Nothing in standard that say whether we are suppose to delete the row
	**   or update the row.  DB2UDB raises error when we try to create the
	**   foreign key fkey4, cloudscape also does the same.
	** 
	**   How we catch the error case ?
	**   Point to note here is the table(t4) we are  adding the foreign key does
	**   not have a problem in this scenarion because we are adding a
    **   a CASACDE link , some other table(t2) that is referring  
	**   can get multiple referential action paths. We can not
	**   this error case for self referencing links.
	**   Algorithm:
	**   -Gather the foreign keys that are
	**   referring(ReferencedKeyConstraintDescriptor) to the table we are adding
	**   foreign key, in our example case we get (fkey3 - table t2 -t4 link)
	**   for each ReferencedKeyConstraintDescriptor
	**   {
	**    1)find the delete connections of the referring table.
	**    [getCurrentDeleteConnections() will return this hash table]
	**	  2) we already have collected the Delete connections 
    **       in validDeleteConnections() for the actual table we are adding the 
    **       foreign key.
	**    3) Now check whether the referring table  is also 
    **       referring  any table that the table we are adding
    **       foreign key has delete connection.
	**
	**     for each table referring table delete connection hash table
	**     {
	**      if it is there in the actual table delete connection hash table
    **      {
	**         //In our example case we find t1 in both the hash tables.
	**         make sure we are having valid referential action
    **         from the existing path and the new path we got from 
	**         new foreign key relation ship.
	**        //In our example case t2 has CASCADE relations with t1
	**        //Because of new foreign key added we also get 
	**        //SET NULL relation ship with t1. This is not valid
	**        //so we should throw error.
    **      }  
	**     }
	** }	
	**/


	private static void checkForAnyExistingDeleteConnectionViolations
	(
	 DataDictionary	dd,
	 TableDescriptor td,
	 int refActionType,
	 Hashtable newDconnHashTable,
	 String myConstraintName
	 )
	throws StandardException
	{

		//We need to check for the condition in this function only when we are
		//adding ref action of type CASCADE
		if(refActionType != StatementType.RA_CASCADE)
			return;
		
		//find the tables that are referring to the table we 
		//are adding the foreign key and check whether we violate their existing rules.
		String addTableName = td.getSchemaName() + "." + td.getName();;
		ConstraintDescriptorList refCDL = dd.getConstraintDescriptors(td);
		int refCDLSize = refCDL.size();
		for (int index = 0; index < refCDLSize; index++)
		{
			ConstraintDescriptor cd = refCDL.elementAt(index);

			if ((cd instanceof ReferencedKeyConstraintDescriptor))
			{
				ConstraintDescriptorList fkcdl = dd.getActiveConstraintDescriptors
					( ((ReferencedKeyConstraintDescriptor)cd).getForeignKeyConstraints(ConstraintDescriptor.ALL));
	
				int size = fkcdl.size();
				if (size == 0) 
				{ 
					continue; 
				}
				
				//Note: More than one table can refer to the same
				//ReferencedKeyConstraintDescriptor, so we need to find all the tables.
				Hashtable dConnHashtable = new Hashtable();
				for (int inner = 0; inner < size; inner++)
				{
					ForeignKeyConstraintDescriptor fkcd = (ForeignKeyConstraintDescriptor) fkcdl.elementAt(inner);
					TableDescriptor fktd = fkcd.getTableDescriptor();
					//Delete rule that we have to the table we are adding the
					// foreign key relation shop
					int raDeleteRuleToAddTable = fkcd.getRaDeleteRule();

					//This check should not be done on self referencing references.
					if(!fkcd.isSelfReferencingFK())
					{

						//gather the delete connections of the table that is
						//referring to the table we are adding foreign key relation ship

						getCurrentDeleteConnections(dd, fktd, -1, dConnHashtable, false, true);

						/*
						**Find out if we introduced more than one delete connection
						**paths to the table that are referring the table we adding
						**the foreign key relatiosn ship.
						**If we have multiple paths they should have the same type
						**referential action and only one SET NULL path.
						**/

						for (Enumeration e = dConnHashtable.keys() ; e.hasMoreElements() ;) 
						{
							String tName = (String) e.nextElement();
							//we should not check for the table name to which  we are
							//adding the foreign key relation ship.
							if(!tName.equals(addTableName))
							{
								if(newDconnHashTable.containsKey(tName))
								{
									int currentDeleteRule = ((Integer)	dConnHashtable.get(tName)).intValue();
									if((currentDeleteRule == StatementType.RA_SETNULL
										&& raDeleteRuleToAddTable == StatementType.RA_SETNULL) ||
									   currentDeleteRule  != raDeleteRuleToAddTable)
									{
										throw
											generateError(SQLState.LANG_DELETE_RULE_CANT_BE_CASCADE_MPATH, 
														  myConstraintName);
									}
								}
							}
						}
					}
					//same hash table can be used for the other referring tables
					//so clear the hash table.
					dConnHashtable.clear();
				}
			}
		}
	}


	
	private static StandardException generateError(String messageId, 
												   String myConstraintName)
	{
		String message = MessageService.getTextMessage(messageId);
		return StandardException.newException(SQLState.LANG_DELETE_RULE_VIOLATION, 
												myConstraintName, message);
	}	

	private static StandardException generateError(String messageId, 
												   String myConstraintName, 
												   int raRule)
	{
		String raRuleStringId;
		switch (raRule){
		case StatementType.RA_CASCADE:
			raRuleStringId = SQLState.LANG_DELETE_RULE_CASCADE;
			break;
		case StatementType.RA_RESTRICT:
			raRuleStringId = SQLState.LANG_DELETE_RULE_RESTRICT;
				break;
		case StatementType.RA_NOACTION:
			raRuleStringId = SQLState.LANG_DELETE_RULE_NOACTION;
			break;
		case StatementType.RA_SETNULL:
			raRuleStringId = SQLState.LANG_DELETE_RULE_SETNULL;
			break;
		case StatementType.RA_SETDEFAULT:
			raRuleStringId = SQLState.LANG_DELETE_RULE_SETDEFAULT;
			break;
		default: 
			raRuleStringId =SQLState.LANG_DELETE_RULE_NOACTION ; // NO ACTION (default value)
		}

		String raRuleMessageString = MessageService.getTextMessage(raRuleStringId); 
		String message = MessageService.getTextMessage(messageId, raRuleMessageString);
		return StandardException.newException(SQLState.LANG_DELETE_RULE_VIOLATION, 
												myConstraintName, message);
	}	

	private static StandardException generateError(String messageId, 
												   String myConstraintName,
												   String refTableName)
	{

		String message = MessageService.getTextMessage(messageId, refTableName);
		return StandardException.newException(SQLState.LANG_DELETE_RULE_VIOLATION, 
												myConstraintName, message);
	}	

}