Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As far as I know views cannot have a primary key.
However the result of

SELECT FROM KEY_COLUMN_USAGE

says differ.

     PRIMARY            | S_MOBILE_ITEM_VIEW          | ID                 |
     ID                 | S_MOBILE_ITEM_VIEW          | ID                 |
     ID                 | S_MOBILE_ITEM_VIEW          | MOBILE_ID          |
     FKB654BB60D311E75  | S_MOBILE_ITEM_VIEW          | DEL_PACKAGE_ID     |

Are these really primary key and other constraints on a view ?
While my app uses Hibernate, this "primary key on a view" upsets Hibernate so it won't let me do some O-R mapping.
Can I delete records from KEY_COLUMN_USAGE so that I can do some stuff without constraint violation that I don't believe acually exists ?

Oh ps that S_MOBILE_ITEM_VIEW IS a view.

SELECT table_name, table_type FROM information_schema.tables WHERE table_schema ='****';

 S_MOBILE_ITEM_VIEW          | VIEW       |


EDIT
I thought it would look ugly so I didn't post the result of show create table but here we go

delimiter $$

CREATE ALGORITHM=UNDEFINED DEFINER=`yoursmart`@`***.***.***.***` SQL SECURITY DEFINER VIEW `S_MOBILE_ITEM_VIEW` AS select `SM`.`MOBILE_ID` AS `MOBILE_ID`,`SM`.`NAME` AS `MOBILE_NAME`,`SM2`.`NAME` AS `MOBILE_OWNER`,`SIH`.`ID` AS `ID`,`SIH`.`NAME` AS `NAME`,`SIH`.`NAME_ENG` AS `NAME_ENG`,`SIH`.`MANUFACTURER` AS`MANUFACTURER`,`SIH`.`MANUFACTURER_ID` AS `MANUFACTURER_ID`,`SIH`.`PRICE` AS `PRICE`,`SIH`.`IS_ONSALE` AS `IS_ONSALE`,`SIH`.`LIMIT_AMOUNT` AS `LIMIT_AMOUNT`,`SIH`.`ROUND_SCALE` AS `ROUND_SCALE`,`SIH`.`PARENT_ID` AS `PARENT_ID`,`SID`.`BODY` AS `BODY`,`SID`.`REFUND_NOTICE` AS `REFUND_NOTICE`,`SID`.`DELIVERY_NOTICE` AS `DELIVERY_NOTICE`,`SID`.`DETAIL` AS `DETAIL`,`SID`.`DESCRIPTION` AS `DESCRIPTION`,`SID`.`MSG_USE` AS `MSG_USE`,`SID`.`LINK_MSG` AS `LINK_MSG`,`SMIO`.`ORDER_LEVEL` AS `ORDER_LEVEL`,0 AS `LIKEITEM`,`SIH`.`DELIVERY_TYPE` AS `DELIVERY_TYPE`,`SIH`.`DEL_STANDAR_AMOUNT` AS `DEL_STANDAR_AMOUNT`,`SIH`.`DEL_PACKAGE_ID` AS `DEL_PACKAGE_ID`,`SIH`.`DEL_QTY` AS `DEL_QTY`,`SIH`.`DEL_AMOUNT` AS `DEL_AMOUNT`,0 AS `PACKAGE_AMOUNT`,(select count(0) AS `COUNT(*)` from `S_MOBILE_ITEM_ORDER` where (`S_MOBILE_ITEM_ORDER`.`ITEM_ID` = `SIH`.`ID`)) AS `SPONSORCNT`,(select count(0) AS `COUNT(*)` from `S_LIKE_ITEM` where (`S_LIKE_ITEM`.`ITEM_ID` = `SIH`.`ID`)) AS `LIKEITEMCNT`,(select count(0) AS `COUNT(*)` from `S_ITEM_REPLY` where ((`S_ITEM_REPLY`.`ITEM_ID` = `SIH`.`ID`) and (`S_ITEM_REPLY`.`MOBILE_ID` = `SM`.`MOBILE_ID`))) AS `REPLYCNT`,(select count(0) AS `COUNT(*)` from (`S_ITEM_CATEGORY` `SIC` join `S_MOBILE_CATEGORY_ITEM_VIEW` `SMCIV`) where ((`SIC`.`ITEM_ID` = `SMCIV`.`ID`) and (`SIC`.`CATEGORY_ID` = 1) and (`SIC`.`ITEM_ID` = `SIH`.`ID`) and (`SMCIV`.`MOBILE_ID` = `SM`.`MOBILE_ID`))) AS `BRAND_CNT` from ((((`S_ITEM_HEAD` `SIH` left join `S_ITEM_DETAILS` `SID` on((`SIH`.`ID` = `SID`.`ID`))) join `S_MOBILE_ITEM_ORDER` `SMIO` on((`SIH`.`ID` = `SMIO`.`ITEM_ID`))) join `S_MOBILE` `SM` on((`SM`.`MOBILE_ID` = `SMIO`.`MOBILE_ID`))) join `S_MEMBER` `SM2` on((`SM2`.`ID` = `SM`.`MOBILE_ID`))) where ((now() between `SIH`.`EXPOSURE_BEGIN` and `SIH`.`EXPOSURE_END`) and (`SMIO`.`DISPLAY` = _utf8'1') and (`SIH`.`APPROVAL` = _utf8'1'))$$
share|improve this question
    
Can you shows the definition of S_MOBILE_ITEM_VIEW? Which version of MySQL are you using? –  ypercube May 27 '13 at 8:01
    
I've added the result of show create table. see edit plz –  PerfectGundam May 27 '13 at 8:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.