主外键,guid主键,触发器等基础知识

SQL中主键和外键的区别?

主键:
能 够唯一表示数据表中的每个记录的【字段】或者【字段】的组合就称为主码(主键)。 一个主键是唯一识别一个表的每一记录，但这只是其作用的一部分，主键的主要作用是将记录和存放在其他表中的数据进行关联。在这一点上，主键是不同表中各记 录之间的简单指针。主键约束就是确定表中的每一条记录。主键不能是空值。唯一约束是用于指定一个或多个列的组合值具有惟一性，以防止在列中输入重复的值。 所以，主键的值对用户而言是没有什么意义，并且和它要赋予的值也没有什么特别的联系。
外键：
若有两个表A，B，C是A的主键，而B中也有C字段，则C就是表B的外键。外键约束主要用来维护两个表之间数据的一致性。

A为基本表，B为信息表

 

 

在我们的数据库设计中，不可逃避的就是数据库表的主键，可能有很多朋友没有深入思考过，主键的设计对整个数据库的设计影响很大，因此我们不得不要重视起来。 
主键的必要性: 
     　有些朋友可能不提倡数据库表必须要主键，但在我的思考中，觉得每个表都应该具有主键，不管是单主键还是双主键，主键的存在就代表着表结构的完整性，表的 记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，本记录的修改与删除，当我们没有主键时，这些操作会变的非常麻烦。 
主键的无意义性：
     　我强调主键不应该具有实际的意义，这可能对于一些朋友来说不太认同，比如订单表吧，会有“订单编号”字段，而这个字段呢在业务实际中本身就是应该具有唯 一性，具有唯一标识记录的功能，但我是不推荐采用订单编号字段作为主键的，因为具有实际意义的字段，具有“意义更改”的可能性，比如订单编号在刚开始的时 候我们一切顺利，后来客户说“订单可以作废，并重新生成订单，而且订单号要保持原订单号一致”，这样原来的主键就面临危险了。因此，具有唯一性的实际字段 也代表可以作为主键。因此，我推荐是新设一个字段专门用为主键，此主键本身在业务逻辑上不体现，不具有实际意义。而这种主键在一定程序增加了复杂度，所以 要视实际系统的规模大小而定，对于小项目，以后扩展不会很大的话，也查允许用实际唯一的字段作主键的。 
主键的选择
    　我们现在在思考一下，应该采用什么来作表的主键比较合理，申明一下，主键的设计没有一个定论，各人有各人的方法，哪怕同一个，在不同的项目中，也会采用不同的主键设计原则。 

 第一：编号作主键 
      　此方法就是采用实际业务中的唯一字段的“编号”作为主键设计，这在小型的项目中是推荐这样做的，因为这可以使项目比较简单化，但在使用中却可能带来一些 麻烦，比如要进行“编号修改”时，可能要涉及到很多相关联的其他表，就象黎叔说的“后果很严重”;还有就是上面提到的“业务要求允许编号重复时”，我们再 那么先知，都无法知道业务将会修改成什么? 

第二：自动编号主键 
      　这种方法也是很多朋友在使用的，就是新建 一个id字段，自动增长，非常方便也满足主键的原则，优点是：数据库自动编号，速度快，而且是增量增长，聚集型主键按顺序存放，对于检索非常有利;数字型 的，占用空间小，易排序，在程序中传递也方便;如果通过非系统增加记录（比如手动录入，或是用其他工具直接在表里插入新记录，或老系统数据导入）时，非常 方便，不用担心主键重复问题。 
      　缺点：其实缺点也就是来自其优点，就是因为自动增长，在手动要插入指定id的记录时会显得麻 烦，尤其是当系统与其他系统集成时，需要数据导入时，很难保证原系统的id不发生主键冲突（前提是老系统也是数字型的）;如果其他系统主键不是数字型那就 麻烦更大了，会导致修改主键数据类型了，这也会导致其他相关表的修改，后果同样很严重;就算其他系统也是数字型的，在导入时，为了区分新老数据，可能想在 老数据主键前统一加一个“o”(old)来表示这是老数据，那么自动增长的数字型又面临一个挑战。 

第三：max加一 
      　由于自动编号存在那些问题，所以有些朋友就采用自己生成，同样是数字型的，只是把自动增长去掉了，采用在insert时，读取max值后加一，这种方法 可以避免自动编号的问题，但也存在一个效率问题，如果记录非常大的话，那么max()也会影响效率的;更严重的是并发性问题，如果同时有两人读到相同的 max后，加一后插入的id值会重复，这已经是有经验教训的了。 

第四：自制加一 
     　考虑max加一的效率后， 有人采用自制加一，也就是建一个特别的表，字段为：表名，当前序列值。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，有人可能发 现，也可能会存在并发处理，这个并发处理，我们可以采用lock线程的方式来避免，在生成此值的时，先lock，取到值以后，再unlock出来，这样不 会有两人同时生成了。这比max加一的速度要快多了。但同样存在一个问题：在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与 导入后的保持一致，而且数字型都存在上面讲到的“o”老数据的导入问题。因此在“自制加一”中可以把主键设为字符型的。字符型的自制加一我倒是蛮推荐的， 应该字符型主键可以应付很多我们意想不到的情况。  

 第五：guid主键 
    　目前一个比较好的主键是采用guid， 当然我是推荐主键还是字符型的，但值由guid生成，guid是可以自动生成，也可以程序生成，而且键值不可能重复，可以解决系统集成问题，几个系统的 guid值导到一起时，也不会发生重复，就算有“o”老数据也可以区分，而且效率很高，在.net里可以直接使用 system.guid.newguid()进行生成，在sql里也可以使用 newid()生成。优点是： 
    　同 identity 列相比，uniqueidentifier 列可以通过 newid() 函数提前得知新增加的行 id，为应用程序的后续处理提供了很大方便。 
    　便于数据库移植，其它数据库中并不一定具有 identity 列，而 guid 列可以作为字符型列转换到其它数据库中，同时将应用程序中产生的 guid 值存入数据库，它不会对原有数据带来影响。 
    　便于数据库初始化，如果应用程序要加载一些初始数据， identity 列的处理方式就比较麻烦，而 uniqueidentifier 列则无需任何处理，直接用 t-sql 加载即可。 
    　便于对某些对象或常量进行永久标识，如类的 classid，对象的实例标识，uddi 中的联系人、服务接口、tmodel标识定义等。 
缺点是： 
    　guid 值较长，不容易记忆和输入，而且这个值是随机、无顺序的。

    　guid 的值有 16 个字节，与其它那些诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键，可能会带来两方面的消极影响：存储空间增大；索引时间较慢。  

 

 CREATE   TRIGGER   trigger_name    
  ON   {   table   |   view   }    
  [   WITH   ENCRYPTION   ]    
  {  
          {   {   FOR   |   AFTER   |   INSTEAD   OF   }   {   [   INSERT   ]   [   ,   ]   [   UPDATE   ]   }  
                  [   WITH   APPEND   ]  
                  [   NOT   FOR   REPLICATION   ]  
                  AS  
                  [   {   IF   UPDATE   (   column   )  
                          [   {   AND   |   OR   }   UPDATE   (   column   )   ]  
                                  [   ...n   ]  
                  |   IF   (   COLUMNS_UPDATED   (   )   {   bitwise_operator   }   updated_bitmask   )  
                                  {   comparison_operator   }   column_bitmask   [   ...n   ]  
                  }   ]    
                  sql_statement   [   ...n   ]    
          }    
  }    
   
  参数  
  trigger_name  
   
  是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。  
   
  Table   |   view  
   
  是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。  
   
  WITH   ENCRYPTION  
   
  加密   syscomments   表中包含   CREATE   TRIGGER   语句文本的条目。使用   WITH   ENCRYPTION   可防止将触发器作为   SQL   Server   复制的一部分发布。  
   
  AFTER  
   
  指定触发器只有在触发   SQL   语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。  
   
  如果仅指定   FOR   关键字，则   AFTER   是默认设置。  
   
  不能在视图上定义   AFTER   触发器。  
   
  INSTEAD   OF  
   
  指定执行触发器而不是执行触发   SQL   语句，从而替代触发语句的操作。  
   
  在表或视图上，每个   INSERT、UPDATE   或   DELETE   语句最多可以定义一个   INSTEAD   OF   触发器。然而，可以在每个具有   INSTEAD   OF   触发器的视图上定义视图。  
   
  INSTEAD   OF   触发器不能在   WITH   CHECK   OPTION   的可更新视图上定义。如果向指定了   WITH   CHECK   OPTION   选项的可更新视图添加   INSTEAD   OF   触发器，SQL   Server   将产生一个错误。用户必须用   ALTER   VIEW   删除该选项后才能定义   INSTEAD   OF   触发器。  
   
  {   [DELETE]   [,]   [INSERT]   [,]   [UPDATE]   }  
   
  是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。  
   
  对于   INSTEAD   OF   触发器，不允许在具有   ON   DELETE   级联操作引用关系的表上使用   DELETE   选项。同样，也不允许在具有   ON   UPDATE   级联操作引用关系的表上使用   UPDATE   选项。  
   
  WITH   APPEND  
   
  指定应该添加现有类型的其它触发器。只有当兼容级别是   65   或更低时，才需要使用该可选子句。如果兼容级别是   70   或更高，则不必使用   WITH   APPEND   子句添加现有类型的其它触发器（这是兼容级别设置为   70   或更高的   CREATE   TRIGGER   的默认行为）。有关更多信息，请参见   sp_dbcmptlevel。  
   
  WITH   APPEND   不能与   INSTEAD   OF   触发器一起使用，或者，如果显式声明   AFTER   触发器，也不能使用该子句。只有当出于向后兼容而指定   FOR   时（没有   INSTEAD   OF   或   AFTER），才能使用   WITH   APPEND。以后的版本将不支持   WITH   APPEND   和   FOR（将被解释为   AFTER）。  
   
  NOT   FOR   REPLICATION  
   
  表示当复制进程更改触发器所涉及的表时，不应执行该触发器。  
   
  AS  
   
  是触发器要执行的操作。  
   
  sql_statement  
   
  是触发器的条件和操作。触发器条件指定其它准则，以确定   DELETE、INSERT   或   UPDATE   语句是否导致执行触发器操作。  
   
  当尝试   DELETE、INSERT   或   UPDATE   操作时，Transact-SQL语句中指定的触发器操作将生效。  
   
  触发器可以包含任意数量和种类的   Transact-SQL   语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的   Transact-SQL   语句常常包含控制流语言。CREATE   TRIGGER   语句中使用几个特殊的表：    
   
  deleted   和   inserted   是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要 检索   deleted   表中的所有值，请使用：    
  SELECT   *  
  FROM   deleted  
   
  如果兼容级别等于   70，那么在   DELETE、INSERT   或   UPDATE   触发器中，SQL   Server   将不允许引用   inserted   和   deleted   表中的   text、ntext   或   image   列。不能访问   inserted   和   deleted   表中的   text、ntext   和   image   值。若要在   INSERT   或   UPDATE   触发器中检索新值，请将   inserted   表与原始更新表联接。当兼容级别是   65   或更低时，对   inserted   或   deleted   表中允许空值的text、ntext   或   image   列，将返回空值；如果这些列不可为空，则返回零长度字符串。    
  当兼容级别是   80   或更高时，SQL   Server   允许在表或视图上通过   INSTEAD   OF   触发器更新   text、ntext   或   image   列。  
   
  n  
   
  是表示触发器中可以包含多条   Transact-SQL   语句的占位符。对于   IF   UPDATE   (column)   语句，可以通过重复   UPDATE   (column)   子句包含多列。  
   
  IF   UPDATE   (column)  
   
  测试在指定的列上进行的   INSERT   或   UPDATE   操作，不能用于   DELETE   操作。可以指定多列。因为在   ON   子句中指定了表名，所以在   IF   UPDATE   子句中的列名前不要包含表名。若要测试在多个列上进行的   INSERT   或   UPDATE   操作，请在第一个操作后指定单独的   UPDATE(column)   子句。在   INSERT   操作中   IF   UPDATE   将返回   TRUE   值，因为这些列插入了显式值或隐性   (NULL)   值。  
   
   
   
  说明     IF   UPDATE   (column)   子句的功能等同于   IF、IF...ELSE   或   WHILE   语句，并且可以使用   BEGIN...END   语句块。有关更多信息，请参见控制流语言。    
   
   
  可以在触发器主体中的任意位置使用   UPDATE   (column)。  
   
  column  
   
  是要测试   INSERT   或   UPDATE   操作的列名。该列可以是   SQL   Server   支持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，请参见数据类型。    
   
  IF   (COLUMNS_UPDATED())    
   
  测试是否插入或更新了提及的列，仅用于   INSERT   或   UPDATE   触发器中。COLUMNS_UPDATED   返回   varbinary   位模式，表示插入或更新了表中的哪些列。  
   
  COLUMNS_UPDATED   函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含   8   列以上，则   COLUMNS_UPDATED   返回多个字节，最左边的为最不重要的字节。在   INSERT   操作中   COLUMNS_UPDATED   将对所有列返回   TRUE   值，因为这些列插入了显式值或隐性   (NULL)   值。  
   
  可以在触发器主体中的任意位置使用   COLUMNS_UPDATED。  
   
  bitwise_operator  
   
  是用于比较运算的位运算符。  
   
  updated_bitmask  
   
  是整型位掩码，表示实际更新或插入的列。例如，表   t1   包含列   C1、C2、C3、C4   和   C5。假定表   t1   上有   UPDATE   触发器，若要检查列   C2、C3   和   C4   是否都有更新，指定值   14；若要检查是否只有列   C2   有更新，指定值   2。  
   
  comparison_operator  
   
  是比较运算符。使用等号   (=)   检查   updated_bitmask   中指定的所有列是否都实际进行了更新。使用大于号   (>)   检查   updated_bitmask   中指定的任一列或某些列是否已更新。  
   
  column_bitmask  
   
  是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。