MySQL 判断和删除索引和表外键 ---- Procedure Of Delete Index--DROP PROCEDURE IF EXISTS DEL_IDX;CREATE PROCEDURE DEL_IDX(IN P_TABLE_NAME varchar(200), IN P_IDX_NAME VARCHAR(200))BEGINDECLARE ST VARCHAR(250); SET @ST=CONCAT(' DROP INDEX ', P_I
 
MySQL 判断和删除索引和表外键
 
--
 
-- Procedure Of Delete Index
 
--
 
DROP PROCEDURE IF EXISTS DEL_IDX;
 
CREATE PROCEDURE DEL_IDX(IN P_TABLE_NAME varchar(200), IN P_IDX_NAME VARCHAR(200))
 
BEGIN
 
DECLARE ST VARCHAR(250);
 
SET @ST=CONCAT(' DROP INDEX ', P_IDX_NAME, ' ON ', P_TABLE_NAME);
 
SELECT count(*) INTO @CNT FROM information_schema.statistics WHERE table_name=P_TABLE_NAME and index_name=P_IDX_NAME;
 
IF @CNT >0 THEN
 
PREPARE stmt FROM @ST;
 
EXECUTE stmt;
 
END IF;
 
END;
 
CALL DEL_IDX('T_NAME, 'IDX_NAME');
 
DROP PROCEDURE IF EXISTS DEL_IDX;
 
--
 
-- Procedure Of Delete FK
 
--
 
DROP PROCEDURE IF EXISTS DEL_FK;
 
CREATE PROCEDURE DEL_FK(IN P_TABLE_NAME varchar(200), IN F_K_NAME VARCHAR(200))
 
BEGIN
 
DECLARE ST VARCHAR(250);
 
SET @ST=CONCAT(' ALTER TABLE ', P_TABLE_NAME, ' DROP FOREIGN KEY ', F_K_NAME);
 
SELECT COUNT(*) INTO @CNT FROM information_schema.key_column_usage WHERE table_name=P_TABLE_NAME and constraint_name=F_K_NAME;
 
IF @CNT >0 THEN
 
PREPARE stmt FROM @ST;
 
EXECUTE stmt;
 
END IF;
 
END;
 
CALL DEL_FK('T_NAM, 'FK_NAME);
 
DROP PROCEDURE IF EXISTS DEL_FK;
 
?
 
本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉
 
本文系统来源：php中文网

 
COLUMN COLUMNS format a30 word_wrapped
 
COLUMN tablename format a15 word_wrapped
 
COLUMN constraint_name format a15 word_wrapped
 
SELECT TABLE_NAME,
 
CONSTRAINT_NAME,
 
CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
 
NVL2(CNAME3, ',' || CNAME3, NULL) ||
 
NVL2(CNAME4, ',' || CNAME4, NULL) ||
 
NVL2(CNAME5, ',' || CNAME5, NULL) ||
 
NVL2(CNAME6, ',' || CNAME6, NULL) ||
 
NVL2(CNAME7, ',' || CNAME7, NULL) ||
 
NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
 
FROM (SELECT B.TABLE_NAME,
 
B.CONSTRAINT_NAME,
 
MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
 
MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
 
MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
 
MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
 
MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
 
MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
 
MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
 
MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
 
COUNT(*) COL_CNT
 
FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
 
SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
 
SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
 
POSITION
 
FROM USER_CONS_COLUMNS) A,
 
USER_CONSTRAINTS B
 
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 
AND B.CONSTRAINT_TYPE = 'R'
 
GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 
WHERE COL_CNT > ALL
 
(SELECT COUNT(*)
 
FROM USER_IND_COLUMNS I
 
WHERE I.TABLE_NAME = CONS.TABLE_NAME
 
AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
 
CNAME6, CNAME7, CNAME8)
 
AND I.COLUMN_POSITION <= CONS.COL_CNT
 
GROUP BY I.INDEX_NAME)
 
/
 
这就是微学网-程序员之家为你提供的"Oracle中检查外键是否有索引的SQL脚本分享"希望对你有所帮助.本文来自网络，转载请注明出处：http://www.weixuecn.cn/article/6182.html

最近在学sql语言，遇到了两个小问题，记录一下
 1.创建表时，添加外键约束
 
创建表时，添加外键约束的完整语句是：
`constraint 外键名称 foreign key （外键列名称） references 主表名称（主表列名称）`，
 
"constraint 外键名称"可以省略，不设置外键名称。
 但省略后删除外键时，又需要外键名。
 
删除外键的语句：
ALTER TABLE 表名 DROP FOREIGN KEY 外键;
 
这个时候就不太好办了，虽然上面省略constraint 外键名称之后，我们没有设置外键名称，但系统是给了一个外键名的。
 
 我这里给的是‘emp_idfk_1’用系统给的外键名删除外键
 
ALTER TABLE emp DROP FOREIGN KEY  emp_idfk_1;
 
还是报错，所以还是不要省略“constraint 外键名” 比较方便
 2.条件查询 是where不是while
 
创建部门表
CREATE TABLE dept(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
);
 
 创建员工表
CREATE TABLE emp (
	 id INT PRIMARY KEY AUTO_INCREMENT,
	 NAME VARCHAR(10),
	 gender CHAR(1), -- 性别
	 salary DOUBLE, -- 工资
	 join_date DATE, -- 入职日期
	 dept_id INT,
	 FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) ) 
	);
 
-- -- 查询员工的入职日期是2011年11月11日之后的员工信息和部门信息
SELECT * FROM dept t1 ,(SELECT * FROM emp WHILE emp.`join_date`>'2011-11-11') t2 
WHILE t1.id=t2.dept_id;
 
刚开始怎么都找不到错，后来发现，原来是WHERE,不是WHILE。。。
 WHILE是条件满足时一直执行
 WHERE是筛选条件满足的数据

什么是外键：
 如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）
 
外键的作用：
 ①为了一张表记录的数据不要太过冗余。
 
②保持数据的一致性、完整性。
 
是否有必要使用外键？
 正方（需要）
 
 1.数据一致性

 由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据 的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。

 eg：数据库和应用是一对多的关系，Ａ应用会维护他那部分数据的完整性，系统一变大时，增加了Ｂ应用，Ａ和Ｂ两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性，而且一年以后如果又增加了C应用呢？

 2.ER图可靠性

 有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。

 3.使设计更全面

 外键在一定程度上说明的业务逻辑，会使设计周到具体全面

 4.级联性能未必最低

 除非能证明触发器的性能和可维护性比外键更优，否则凭什么一定要否定外键呢？



 5.程序能完全保证安全性吗？

 使用应用程序来维护数据完整性，则是严重降低数据库安全性的一种做法。连接字符串里如果有uid和pwd或者类似的这两个东西，可以使用工具获取你数据库的帐号。因为每次建立数据库联接的时候都会在网线上以标准格式传递这些信息。从此编码内的任何保证都是空的。

 6.导致冗余

 不使用外键，会导致数据冗余，在级联最底层的表可能会重复好几层的数据 必然导致最底层的表数据量翻倍，IO瓶颈是数据库性能瓶颈之一。
 
 
反方（不需要）
 
1.程序逻辑
 
  某些程序逻辑中，程序的逻辑已经足够保证完整性，我会在存储过程或包等地方做严谨的判断；
 
2.性能问题
 
这是很多人不喜欢用的关键原因，比如一个业务流水表，频繁插入数据，如果这个表身上有3外键，那么每次插入一条，就必须对这3个外键对应的3个表做相应的查找判断有无对应数据，如果这3个表也很大，那就这3个表的判断时间就很常，虽然外键指向的关联表的字段肯定是索引，但是我觉得很多时候，这样的判断本来就在程序里控制好了，通过外键再判断一次，就是降低性能；而且其实有的地方判不判断也无所谓的，但是用了外键，就必须化时间去判断，无论oracle内部多么优化外键对于数据的检索速度，它总是一个不小的消耗；
 
3.维护麻烦
 
很多公司的软件都是定制的，这种定制的东西，随意性相对较大，项目开发实施过程中，需要经常对表修修补补；还有就是业务逻辑有bug或者其他情况，需要经常手工维护数据，有错综复杂的外键关联着，很是麻烦；
 
4.外键定死了先后生成关系

外键定死了两个表之间数据的先后生成关系，最常见的是单据主从表，有的时候，在生成单据的时候，是先生成明细，再生成主表；如果钉死了外键，这个就没法实现；

 PS: 当然有些关键的业务，确实需要外键；
 
为什么不用外键？
 数据库外键争论（http://www.cnblogs.com/chenkai/archive/2009/11/11/1601193.html?page=2#commentform）
 下面的话全是上面争论里的原话，我就截取了三个人的，因为我个人也偏向不使用外键
 
1、淡化外键，并不能简单武断的说淡化或不淡化。 必须根据实际情况来决定，如数据来源，关联影响，业务控制逻辑等。
 以下是个人关于是否淡化外键的个人经验和想法。
 
淡化外键的情况：如果对于某此数据来源复杂，且单表操作对系统基本没有什么影响的情况下，可以淡化外键。 何谓数据来源复杂？数据的来源不止仅限于自由系统，更可能来源于其它系统，或人工的数据导入，并且导入的数据不全面，在这种情况下，淡化外键可以极大减少工作量，并且极大的降低工作的复杂度。
 不能淡化外键的情况：关键数据，业务逻辑很严谨，数据来源单一，数据来源规则标准固定的。业务逻辑严谨是指数据表之间的关联很好，变动一个表的数据，必须对另一个表产生影响，有时这种影响还相当的大。数据来源单一是指数据都从自身系统来，或固定的其它系统通过一定的逻辑录入；数据来源规则标准固定是指从其它地方人工录入或是对过库对库的数据录入或同步时，数据规则是一致的。 在这种情况下，根据约束完整性设置外键，一方面能保证系统的健壮性，另一方面可以及排除淡化外键的情况可能出现的一些数据问题（你懂的，很多时候是人工操作，或来源数据不全导致的一些问题）。
 完美的系统一定不是最合适的系统，依据现实而言，只需要最合适的。
 
2、几乎不用，抛开性能不说，开发、测试、部署、实施，以及维护的时候都带来不少问题
 数据完整性几乎都是业务的要求，理应由业务部分负责维护，而不是依赖数据库
 访问量较大的web应用，以及有一定规模的企业应用，都关注伸缩性和性能问题，各种形式的垂直、水平切分运用越来越多，外键、触发器、存储过程之类的基本属禁区
 
3、我的建议是:
 1.尽量不使用外键.
 2.被关联的表一般不删除数据,只是把数据标示为"已删除".