设置MySQL数据表主键

设置MySQL数据表主键： 使用“primary key”关键字创建主键数据列。被设置为主键列不允许出现重复的值，很多情况下与“auto_increment”递增数字相结合。如下SQL语句所示：

Mysql>create table books(bookid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,bookname varchar(50));
Mysql>insert into books(bookname) values(“book1”),(“book2”),(“book3”);
Mysql>select * from books;

若要修改列主键或类型，请参考 ALTER TABLE 语句。 设置MySQL数据表外键 外键是设置当前表中的某一列与别一数据表中的主键列关联。主要目的是控制与外键表中的数据，保持数据一致性，完整性，也就是说：当前表中这一列的数据必须是关联外键列中的某一数据，而且相关联的两个数据列的类型必须相同；当关联外键列某一数据修改或删除时，将触当前表的某一项相应操作。可解发以下事件以及参数： 触发事件：on delete和on update 可设参数：cascade(跟随外键改动)； restrict(限制外表中的外键改动)； set Null(设空值）； set Default（设默认值）； no action [默认] 设置关联的语句由[指定主键关键字：foreign key(列名)]和[引用外键关键字： references <外键表名>(外键列名)]组成。例如创建一个关于books的购物车数据表“gbooks”，其中“gbookid”与books表中的“bookid”创建外键关联。

Mysql>create table gbooks(gid int(11) not null auto_increment primary key,gbookid int(11),goodsum int(4),foreign key(gbookid) references books(bookid) on delete cascade on update cascade);

删除外键： 首先，使用SHOW CREATE TABLE语句查看创建表描述。其中“CONSTRAINT”关键字后面有一个引号括起来的名称，它就是这个表外键的代表，是在创建外键时自动生成的名称，当然在创建的过程中可以直接用“CONSTRAINT”关键字自定义名称。其查看的完整语句如下：

Mysql>show create table gbooks;

这里代表外键的名称是“gbooks_ibfk_1”，目标找到了，再使用ALTER语句进行删除。

Mysql>ALTER TABLE gbooks DROP FOREIGN KEY gbooks_ibfk_1;

主键和外键的设置意义： 主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。 必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境，就很难对这些键进行修改，所以在开发阶段就设计好主键和外键就是非常必要和值得的。 主键： 关系数据库依赖于主键—它是数据库物理模式的基石。主键在物理层面上只有两个用途： 1. 惟一地标识一行。 2. 作为一个可以被外键有效引用的对象。 基于以上这两个用途，下面给出了我在设计物理层面的主键时所遵循的一些原则： 1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。 2. 主键应该是单列的，以便提高连接和筛选操作的效率。 3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。 注：这项原则对于那些经常需要在数据转换或多数据库合并时进行数据整理的数据并不适用。 4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。 5. 主键应当有计算机自动生成。如果由人来对主键的创建进行干预，就会使它带有除了惟一标识一行以外的意义。一旦越过这个界限，就可能产生认为修改主键的动机，这样，这种系统用来链接记录行、管理记录行的关键手段就会落入不了解数据库设计的人的手中。 外键是用来和其他表建立联系用的：这个表中的一列和另外一个表中的一列相同，为了让这两个表联系起来，就把其中一个表中的列设成外键，把另外一个表列设成主键，就实现了这两个表的关联。一个表可以有多个外键。但主键只能有一个。 像MySQL这样的关系型数据库管理系统，它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中建立记录到记录的联系，RDBMS可以利用不同的方法分析数据，同时保持数据库以系统的方式、最小的冗余进行组织。 像MySQL这样的关系型数据库管理系统，它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中建立记录到记录的联系，RDBMS可以利用不同的方法分析数据，同时保持数据库以系统的方式、最小的冗余进行组织。 简单描述： 这些关系基本上依靠外键进行管理，在关系中所有表中具有相同含义的字段作为公共部分来连接不同表中的记录。外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。 MySQL中“键”和“索引”的定义相同， 所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。这和一些封建思想比较沉重的家庭是一样的，外来的孩子(儿媳妇，倒插门女婿)一般都是不受重视的。 低俗示例： 表间一对一关系示例： 有两张表，第一张表是记录公司有多少人，都有谁，也就是员工编号及员工姓名这些基本表。另一张表记录每个月发给用户多少工资，所谓工资表是也。 但是工资表里面不能以员工姓名为主键，同样要通过员工id，因为员工的姓名是可能重复的啊。部门经理叫张三，小弟也叫张三，那这俩张三的工资能一样吗？并且员工表里面的每个人都有工资，否则谁也不给你干活，且一个人只能有一份工资，否则老板也不同意了。所以员工表和工资表是通过员工id进行关联的一对一关系。 不过我们要有一个好的价值观，我们上班不能为了钱，我们是为了学知识，学文化，为早日实现四个现代化(别问我是啥，也别问我到底实现没有)而努力奋斗。所以在工资表里如果没有你也不要乱喊。嗯。 /* 建立员工表 */ create table employees ( id int(5) not null auto_increment , name varchar(8) not null, primary key (id) ) type＝innodb; /* 建立工资表 */ create table payroll( id int(5) not null, emp_id int(5) not null, name varchar(8) not null, payroll float(4,2) not null, primary key(id), index emp_id (emp_id), foreign key (emp_id) references employees (id) ) type = innodb; 表间一对多关系示例： 有两个表，一个是贪官表，有贪官的id和名字。另有一张贪官情妇表，注意一个贪官不一定只有一个情妇，其有个二三四五奶是很正常的，所以在贪官表里面的一条数据，对应情妇表里可能就有多条记录，这是通过贪官id进行关联的一对多关系。 参照完整性： 当外键与另一个表的字段有关系，而且这种关系是惟一时，这个系统就称为处于参照完整性的状态。也就是说，如果一个字段在所有的表中只出现一次，而且每个表的这个字段的变化都会影响其他表，这就是存在参照完整性。 术语理解上可能不太方便，其实就是说要在有外键的表中保持所有数据的一致性。比如说“张三”离职了，在员工表里面肯定没有这个人了，可是如果在工资表里面还存在这个孩子，那么老大就会很生气的。 另外，比如说一个县官，因为一些小政绩，由县官变成了知府，那么他的那些情妇的地位也要调整一下，最起码得从县官二奶改为知府二奶，否则这位二奶也是不会同意的。 MySQL的外键只能在InnoDB表中使用： 当今主流数据库都会自动考虑参照完整性的问题。当你更新或删除数据时，其会把相关联的表中数据也都给你变过来。比如县官张三改名为王二麻子，其情妇的称号就会自动改为王二麻子的情妇。嗯。 MySQL对此一直持观望态度，它允许使用外键，但是为了完整性检验的目的，在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。这可能有些怪异，实际上却非常正常：对于数据库的所有外键的每次插入、更新和删除后，进行完整性检查是一个耗费时间和资源的过程，它可能影响性能，特别是当处理复杂的或者是缠绕的连接树时。因而，用户可以在表的基础上，选择适合于特定需求的最好结合。。 所以，如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。 MySQL创建外键语法： 创建外键的语法是这样的：FOREIGN KEY (当前表的字段名)… REFERENCES 参照表 (参照表的字段名) foreign key (emp_id) references employees (id); 的意思就是说当前表的emp_id字段是以employees的id字段为外键的。 注意事项： 一旦建立外键，MySQL只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”，那么在情妇表只才能有“王二麻子的情妇”。也就是说只有确认一个人是贪官了，才能把其情妇信息列入此表中，否则是不行滴。 关系中的所有表必须是innoDB表，在非InnoDB表中，MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。 用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。 在外键关系中，字段的数据类型必须相似，这对于大小和符号都必须匹配的整数类型尤其重要。 即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误(即使这样做可能会破坏更早创建的外键) 删除外键方法： long long ago，人们只能通过删除表来删除外键。不过现在MySQL(在4.0.13及更高版本中)提供了一种从表中删除外键比较缓和的方法，缓和与否不太清楚，但是至少不再那么无耻。 ALTER TABLE table-name DROP FOREIGN KEY key-id; 这里有一个概念，这个外键的id是啥玩意？我们可以通过SHOW　CREATE TABLＥ 命令来获得key-id的值。日后我们详细讨论这些内容，大家可以自行演示。 /* 显示建表结构语句，key-id为payroll_ibfk_1 */ show create table payroll /G /* *************************** 1. row *************************** Table: payroll Create Table: CREATE TABLE `payroll` ( `id` int(5) NOT NULL, `emp_id` int(5) NOT NULL, `name` varchar(8) NOT NULL, `payroll` float(4,2) NOT NULL, PRIMARY KEY (`id`), KEY `emp_id` (`emp_id`), CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) */ 自动键更新和删除： 外键可以保证新插入的记录的完整性。但是，如果在REFERENCES从句中从已命名的表删除记录会怎样？在使用同样的值作为外键的辅助表中会发生什么？ 很明显，那些记录也应该被删除，否则在数据库中就会有很多无意义的孤立记录。MySQL可能通过向FOREIGN KEY…REFERENCES 修饰符添加一个ON DELETE或ON UPDATE子句简化任务，它告诉了数据库在这种情况如何处理孤立任务。 请注意，通过 ON UPDATE　和ON DELETE规则，设置ＭｙＳＱＬ能够实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏。例如，如果一系列的表通过外键关系和ON DELETE CASCADE 规则连接时，任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况下被删除。所以，我们在操作之前还是要检查这些规则的，操作之后还要再次检查。 分享到：