Developer Calssess

MYSQL 외래키(Foreign key) 지정 시 

on delete rule(삭제 시), on update rule(변경 시) 옵션 지정

RESTRICT, CASCADE, NO ACTION, SET NULL

1. RESTRICT : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 변경/삭제가 취소됩니다.(제한)

2. CASCADE : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 함께 변경/삭제됩니다.

3. NO ACTION : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 변경/삭제할 개체만 변경/삭제되고 참조하고 있는 개체는 변동이 없습니다.

4. SET NULL : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 참조하고 있는 값은 NULL로 세팅됩니다.

출처: https://h5bak.tistory.com/125 [이준빈은 호박머리]

Foreign Key 제약 설정시 On delete cascade 구문에 대해 헤깔리는 부분이 있어서
정리하였습니다. 테스트한 결과입니다.
 
 
1. 테이블 생성
-- 부모 테이블
create table p1
(
no number(10) not null primary key,
name varchar2(10)
);
-- 자식 : 기본키 속성
create table p2
(
no number(10) not null primary key,
name varchar2(10)
);
-- 자식 : 일반속성
create table p3
(
id number(10) not null primary key,
no number(10)
);

2. 데이터 Insert
insert into P1
select level no ,'A' from dual connect by level <=3;
commit;
insert into P2
select level no ,'A' from dual connect by level <=3;
commit;

insert into P3
select level no , level  from dual connect by level <=3;
commit;

3.제약조건 설정 및 테스트
1) Case 1
alter table p2 add constraint fk_p2 foreign key(no) references p1(no);
alter table p3 add constraint fk_p3 foreign key(no) references p1(no);

- 부모 테이블에서 데이터 삭제시 Default로 생성되는 옵션 "NO ACTION"
- 명시적으로 프로그램에서 자식 테이블의 Row 모두 삭제하고 부모 테이블의 Row를 삭제해야됨

2) Case 2
alter table p2 add constraint fk_p2 foreign key(no) references p1(no) on delete cascade;
alter table p3 add constraint fk_p3 foreign key(no) references p1(no) on delete cascade;
- 부모 테이블에서 데이터 삭제시 자식 테이블의 Row는 자동으로 삭제됨
3) Case 3
alter table p2 add constraint fk_p2 foreign key(no) references p1(no) on delete set null;  ---- PK는 SET NULL 사용불가
alter table p3 add constraint fk_p3 foreign key(no) references p1(no) on delete set null;
- 부모 테이블에서 데이터 삭제시 자식 테이블의 Row는 자동으로 NULL로 업데이트 함
- 기본키 속성에는 위배됨.(Not Null 제약에 의해서)
* 따라서 Foreign Key 제약 조건에 의해 데이터 무결성을 유지하기 위한 방법으로
  -Foreign Key 설정시 On Delete 구문을 추가하지 않으면 Default 옵션은  "No Action"
   .명시적으로 프로그램에서 자식 테이블의 Row를 삭제한후 부모 테이블의 Row를 삭제할수 있음
  -DB에서 자동으로 데이터를 처리할 경우 기본키는 "On Delete cascade", 일반속성은 "On Delete set null"
   를 사용해야 됨.

4.결론
 
  -부모 테이블의 Row에 대해서는 자동으로 자식테이블 까지 삭제 가능(Delete or Set NULL)
  -업데이트는 프로그램을 통해서 명시적으로 구현해야됨
  
* Oracle은 ON UPDATE 구문 사용이 불가능함. 트리거를 이용해서 구현해야 됨
* Syntax 
REFERENCES tableName [ ( simpleColumnName [ , simpleColumnName ]* ) ]
[ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL } ]
   [ ON UPDATE { NO ACTION | RESTRICT } ]
|
[ ON UPDATE { NO ACTION | RESTRICT } ]
   [ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL } ]
 
Restrict    : Disallows the update or deletion of referenced data.
Set to null  : When referenced data is updated or deleted, all associated dependent data is set to NULL.
Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
Cascade    : When referenced data is updated, all associated dependent data is correspondingly updated.
                When a referenced row is deleted, all associated dependent rows are deleted.
No action   : Disallows the update or deletion of referenced data.
 This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred.
(Oracle Database uses No Action as its default action.)

출처 : https://m.blog.naver.com/PostView.nhn?blogId=k65fac&logNo=220868098773&proxyReferer=https:%2F%2Fwww.google.com%2F

WHERE 절의 조합

예제 데이터 : User_Table

SELECT * FROM User_Table ;

---------------------------------------------------------------------------------------------------------------------------

AND 연산자의 사용

입력 -

SELECT user_id , user_mobile , user_gender , user_amount  , user_addr   FROM  User_Table

WHERE user_gender  = 'male' AND user_amount   >= 5500 ;

풀이 :  User_Table 에서 성별(user_gender)이 남자이고 보유금액(user_amount)가 5500 이상인 사람의 정보를 불러왔다.

출력 -

이렇게 WHERE  구절에  AND 연산자를 추가하여 하나 이상의 열로 필터링 할 수 있다.

예제 데이터 user_gender 가 'male' 이고 user_amount 가 5500 이상 인 두명의 정보가 출력 되었다.(두가지 조건이 모두 충족되는 정보)

1차 검색인 user_gender 의 정보가 'male' 이더라도 user_amount 가 5500 미만 이라면 검색 되지 않는다.

AND 연산자는 WHERE  절 뒤에 원하는 만큼 추가하여 정보를 가공 할 수 있다.

입력 -

SELECT user_id , user_mobile , user_gender , user_amount  , user_addr   FROM  User_Table

WHERE user_gender  = 'male' AND user_amount   >= 5500 AND user_addr = 'busan';

출력 -

---------------------------------------------------------------------------------------------------------------------------

OR 연산자의 사용

AND 연산자와 정 반대의 개념인  OR 연산자는 여러가지 조건에서 하나 이상의 조건이 만족되면 데이터를 출력한다.

입력 -

SELECT user_id , user_mobile , user_gender , user_amount  , user_addr   FROM  User_Table

WHERE user_gender  = 'male' OR user_amount   >= 5500 ;

풀이 :  User_Table 에서 성별(user_gender)이 남자인 사람과 보유금액(user_amount)이 5500 이상인 사람의 정보를 불러왔다.

출력 -

위에  예제와 비교해 보면 AND 연산자를 OR 연산자로만 변경 했을뿐인데 결과 값이 완전 틀려진 걸 알 수 있다.

결과 값을 보면 알 수 있듯이  OR 연산자는 두가지 중 한가지라도 만족 하는 정보를 출력한다.

---------------------------------------------------------------------------------------------------------------------------

논리 연산자 평가 순서의 이해

WHERE 절에는 AND 와 OR 연산자를 여러 개 사용할 수도 있다.

이렇게 여러 조건을 결합하면 보다 복잡한 필터링이 가능해진다.

입력 -

SELECT user_id , user_mobile , user_gender , user_amount  , user_addr 

FROM  User_Table

WHERE user_gender  = 'male'

OR user_amount   >= 5500

AND user_addr  IS NULL;

출력 -

풀이 :  User_Table 에서 성별(user_gender)이 남자인 이거나 보유금액(user_amount)이 5500 이상인 사람 중에 주소(user_addr)이 NULL인 정보를 불러왔다. 하지만 해당 출력문에서 보면 알 수 있듯이 원하던 정보가 나오지 않았다. 이럴 경우 에는 ( ) 로 평가를 구분 해줄 수 있다.

->

SELECT user_id , user_mobile , user_gender , user_amount  , user_addr 

FROM  User_Table

WHERE (user_gender  = 'male' OR user_amount   >= 5500)

AND user_addr  IS NULL;

출력 -

풀이 : 기존에 원했던 정보 처럼 User_Table 에서 성별(user_gender)이 남자인 이거나 보유금액(user_amount)이 5500 이상인 사람 중에 주소(user_addr)이 NULL인 정보를 불러왔다.

※ WHERE  절에서 AND OR 같은 논리 연산자를 사용할때는 각 연산자를 모두 괄호로 묶어주는 것이 좋다  기본 평가 순서에 따라 제대로 해석되리라고 안심하지 말고, 직접 순서를 정해 의도대로 묶어주자. 괄호 사용에 따른 단점은 전혀 없으며, 오히려 조건을 보다 명확하게 이해하는데 도움이 된다.

---------------------------------------------------------------------------------------------------------------------------

IN 연산자의 사용

- 여러 값을 OR 관계로 묶어 나열하는 조건을 WHERE  절에 사용할 때 쓸 수 있는 키워드

IN 연산자는 조건의 범위를 지정하는 데 사용된다. 값은 콤마( , )로 구분하여 괄호 내에 묶으며, 이 값 중에서 하나 이상과 일치하면 조건에 맞는 것으로 평가된다.

입력 -

SELECT user_num  ,  user_id , user_mobile , user_gender , user_amount  , user_addr  

FROM  User_Table

WHERE user_id  IN ('user1','user3')

ORDER BY user_num DESC; 

풀이 :  User_Table 에서 사용자의 아이디(user_id)가 'user1'인 사람과 'user3' 인 사람의 정보를 user_num 를 통해 역순으로 정렬하여 불러왔다.

출력 -

위에 쓰임새를 보면 OR 연산자와 동일하다고 느낄 수 있다. 위으 IN 연산자의 예제를  OR 연산자로 대체해서 사용하자면 이렇게 표현된다.

SELECT user_num  ,  user_id , user_mobile , user_gender , user_amount  , user_addr  

FROM  User_Table

WHERE user_id  = 'user1' OR user_id = 'user3'

ORDER BY user_num DESC; 

출력 -

동일한 값이 출력 되는 걸 알 수 있다.

IN 연산자의 장점

-  목록에 넣을 값이 여러 개일때, IN 연산자가 보다 쓰기도 쉽고, 이해하기도 쉽다.

-  IN을 사용하면 평가 순서를 보다 쉽게 관리 할 수 있고, 연산자 수도 줄어든다.

-  IN 연산자가 OR 연산자보다 실행 속도가 빠르다.

-  IN의 가장 큰 장점은 IN 연산자에 다른 SELECT 문을 넣을 수 있다.

-  동적인 WHERE 절을 만들때 더 크게 활용 된다.

---------------------------------------------------------------------------------------------------------------------------

NOT 연산자의 사용

- 조건을 부정할 때 사용되는 WHERE 절의 키워드

- NOT 연산자는 말그대로 바로 뒤에 오는 조건을 부정하는 역할을 한다. 때문에 혼자서는 되지 않는다.

- 다른 연산자와는 달리 필터링 할 열의 뒤가 아닌 앞에 사용된다.

입력 -

SELECT user_num  ,  user_id , user_mobile , user_gender , user_amount  , user_addr  

FROM  User_Table

WHERE NOT user_id  IN ('user1','user3')

ORDER BY user_num DESC; 

풀이 : 이예제는 IN 예제에서 필터링할 열의 앞에 NOT 연산자 하나만 추가 한것이다. User_Table 에서 사용자의 아이디(user_id)가 'user1'인 사람과 'user3' 인 사람의 정보를 제외한 모든 정보를 user_num 을 통해 역순으로 정렬하여 불러왔다.

출력 -

출력 결과 에서 볼 수 있듯이  NOT 연산자 하나를 추가한 결과로 정 반대의 결과를 가져올 수 있다.

- 해당 조건문이 IN 연산자로 묶이지 않은 하나의 열이라면 굳이 NOT 연산자를 사용하지 않고 !=  또는<> 등의 조건연산자를 사용해도 결과는 동일하다.

위의 예제를 NOT 이나 IN 연산자를 사용하지 않고 동일한 결과를 얻어 오려면 입력문은 아래와 같다.

입력 -

SELECT user_num  ,  user_id , user_mobile , user_gender , user_amount  , user_addr  

FROM  User_Table

WHERE user_id  != 'user1' AND user_id  <> 'user3'

ORDER BY user_num DESC; 

출력 -

출력문의 결과는 동일하다. 좀 더 복잡한 구문에서는 NOT 과 IN 을 사용하는 것이 간결해지는 것 같다.

출처: https://inforyou.tistory.com/28 [a battle with myself]

※뷰(View), 프로시져(Procedure), 트리거(Trigger), 함수(Function) 내부 단어 검색

해당 테이블이 어디에서 사용하는지 한번에 검색하고 싶을때 아래명령을 통하여 검색하면된다.

Function의 경우는 스칼라와 테이블반환이 구분되어 검색된다.

(테스트 해본 결과 뷰(VIEW), 프로시져(SQL_STORED_PROCEDURE), 트리거(SQL_TRIGGER), 함수(SQL_SCALAR_FUNCTION & SQL_TABLE_VALUED_FUNCTION) 타입이 검색되는부분은 확인됨)

DECLARE @SEARCH_TEXT NVARCHAR(MAX) = '검색텍스트';
 
SELECT B.NAME       AS NAME     -- 이름
     , B.TYPE_DESC  AS TYPE     -- 타입 구분
     , A.DEFINITION AS CONTENTS -- 내용
  FROM SYS.SQL_MODULES A WITH (NOLOCK)
  LEFT JOIN SYS.OBJECTS B WITH (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID
 WHERE DEFINITION LIKE '%' + @SEARCH_TEXT + '%'      
 ORDER BY TYPE, NAME  

출처 https://mirwebma.tistory.com/187