Adding Primary Keys and Foreign Keys to a Table

<script language="C#" runat="server">

 void Page_Load(Object sender, EventArgs e)
 {

  // get connection string from ../global/connect-strings.ascx user control
  string strConnect = ctlConnectStrings.OLEDBConnectionString;
  outConnect.InnerText = strConnect; // and display it

  // specify the SELECT statement to extract the BookList data
  string strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE '18610033%'";
  outSelectBooks.InnerText = strSelectBooks;   // and display it

  // specify the SELECT statement to extract the BookAuthor data
  string strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE '18610033%'";
  outSelectAuthors.InnerText = strSelectAuthors;   // and display it

  // declare a variable to hold a DataSet object
  DataSet objDataSet = new DataSet();

  try
   {
   // create a new Connection object using the connection string
   OleDbConnection objConnect = new OleDbConnection(strConnect);

   // create a new Command object
   OleDbCommand objCommand = new OleDbCommand();

   // set the properties
   objCommand.Connection = objConnect;
   objCommand.CommandType = CommandType.Text;
   objCommand.CommandText = strSelectBooks;

   // create a new DataAdapter object
   OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
   // and assign the Command object to it
   objDataAdapter.SelectCommand = objCommand;

   // get the data from the "BookList" table in the database and
   // put it into a table named "Books" in the DataSet object
   objDataAdapter.Fill(objDataSet, "Books");

   // change the SELECT statement in the Command object
   objCommand.CommandText = strSelectAuthors;
   // then get data from "BookAuthors" table into the DataSet
   objDataAdapter.Fill(objDataSet, "Authors");
  }
  catch (Exception objError)
  {
   // display error details
   outError.InnerHtml = "<b>* Error while accesing data</b>.<br />"
     + objError.Message + "<br />" + objError.Source;
   return;  //  and stop execution

  }

  // now that the DataSet is filled, we can modify the tables it contains
  // declare variables to refer to the DataTable and a DataColumn objects
               
  DataTable objParentTable = objDataSet.Tables["Books"];//父表
  DataTable objChildTable = objDataSet.Tables["Authors"];//子表
  DataColumn objParentColumn = objParentTable.Columns["ISBN"];//父表的ISBN列
  DataColumn objChildColumn = objChildTable.Columns["ISBN"];//子表ISBN列

  // create a new UniqueConstraint object and add to Constraints collection
                //创建主键约束
  UniqueConstraint objUnique = new UniqueConstraint("Unique_ISBN", objParentColumn);//Unique_ISBN是约束名，objParentColumn是主键约束列
  objParentTable.Constraints.Add(objUnique);//添加约束
      
  // prevent the column from accepting Null values
  objParentColumn.AllowDBNull = false;

  // create an array of columns containing this column only
               
  DataColumn[] objColumnArray = new DataColumn[1];
  objColumnArray[0] = objParentColumn;

  // and set this array as the columns for the Primary Key of the table
               //指定主键（是一个DataColumn数组，保证只有唯一标号。此处只有ISBN一列）
  objParentTable.PrimaryKey = objColumnArray;

  // now we can process the child table named "Authors"
  // create an array of columns containing the ISBN and Lastname columns
                
  objColumnArray = new DataColumn[2];
  objColumnArray[0] = objChildColumn;    // the ISBN column
  objColumnArray[1] = objChildTable.Columns["Lastname"];

  // prevent either of these columns containing Null
  objColumnArray[0].AllowDBNull = false;
  objColumnArray[1].AllowDBNull = false;
  
  // set this column array as the primary key
             //指定主键（由ISBN和Lastname两列组成的数组）
  objChildTable.PrimaryKey = objColumnArray;

  // create a new ForeignKeyConstraint object
                //指定外键
  ForeignKeyConstraint objFKey = new ForeignKeyConstraint("FK_BookAuthors",  objParentColumn, objChildColumn);
  // set the "update" properties
  objFKey.DeleteRule = Rule.Cascade;//级联
  objFKey.UpdateRule = Rule.Cascade;

  // and add it to the Constraints collection
  objChildTable.Constraints.Add(objFKey);

  // --------------------------------------------------------------
  // now we're ready to display the contents of the DataSet object
  // bind the collection of Tables to the first DataGrid on the page
  dgrTables.DataSource = objDataSet.Tables;
  dgrTables.DataBind();

  // bind the collection of Constraints to the second DataGrid on the page
  dgrBookCons.DataSource = objDataSet.Tables["Books"].Constraints;
  dgrBookCons.DataBind();

  // bind the collection of Constraints to the third DataGrid on the page
  dgrAuthorCons.DataSource = objDataSet.Tables["Authors"].Constraints;
  dgrAuthorCons.DataBind();

  // create a DataView object to use with the tables in the DataSet
  DataView objDataView = new DataView();

  // get the default view of the Books table into the DataView object
  objDataView = objDataSet.Tables["Books"].DefaultView;
  // and bind it to the next DataGrid on the page
  dgrBooksData.DataSource = objDataView;
  dgrBooksData.DataBind();

  // then do the same for the Authors table
  objDataView = objDataSet.Tables["Authors"].DefaultView;
  dgrAuthorsData.DataSource = objDataView;
  dgrAuthorsData.DataBind();

}
</script>