2021.02.18 Oracle SQL

--既存の再生テーブルを削除
DROP TABLE buyTBL;
DROP TABLE userTBL;
--userTBLテーブルの作成
CREATE TABLE userTBL
(
userID CHAR(8),
userName NVARCHAR2(10),
birthYear NUMBER(4),
addr NCHAR(2),
mobile1 CHAR(3),
mobile2 CHAR(8),
height NUMBER(3),
mDate DATE
);
--BuytBLテーブルの作成
CREATE TABLE buyTBL
(
idNum NUMBER(8) PRIMARY KEY,
userID CHAR(8),
prodName NCHAR(6),
groupName NCHAR(4),
price NUMBER(8),
amount NUMBER(3)
);
--シーケンスの削除と再生
DROP SEQUENCE idSEQ;
CREATE SEQUENCE idSEQ;
--userTBLにデータを入力します.金范秀の出生年は知らないで、空の値、金京浩は1871年で、入力は间违います
INSERT INTO USERTBL VALES(「LSG」、「李勝基」、1987、「ソウル」、「011」、「11111111」、182、「2008-8」)
INSERT INTO USERTBL VALES(「KBS」,「金範秀」,「NULL」,「慶南」,「011」,「2222222222」,「173」,「2012-4-4」)
INSERT INTO USERTBL VALES(「KKH」,「金警衛」,1871,「全南」,「019」,「33333333」,177,「2007−7」)
INSERT INTO USERTBL VALUES(「JYP」,「趙容弼」,「試合」,「011」,「44444444」,「166」,「2009-4-4」)
--TBL購入でデータを入力
INSERT INTOはTBL VALuES(idSEQ.NEXTVAL、「KBS」、「スニーカー」、NULL、30、2)を購入した.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「KBS」、「ノートパソコン」、「電子」、1000、1)を購入する.
INSERT INTOはTBVALUES(idSEQ.NEXTVAL,「JYP」,「ディスプレイ」,「電子」,200,1)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「BBK」、「ディスプレイ」、「電子」、200,5)を購入する.
--制約を作成します.(プライマリ・キー制約の作成)
ALTER TABLE userTBL
ADD CONSTRAINT PK_userTBL_userID
PRIMARY KEY(userID);
--プライマリ・キーの確認
SELECT * FROM USER_CONSTRAINTS
WHERE OWNER = 'TABLEDB' AND TABLE_NAME = 'USERTBL' AND CONSTRAINT_TYPE = 'P';
DESCRIBE userTBL;
--外部キーの設定
ALTER TABLE BuytBL-- userID列に外部キーを設定
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL (userID);

--오류 발생.
--ORA-02298: cannot validate (TABLEDB.FK_USERTBL_BUYTBL) - parent keys not found
--02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
--*Cause:    an alter table validating constraint failed because the table has
--           child records.
--*Action:   Obvious

--BBK行を削除し、外部キーを再設定
DELETE FROM buyTBL WHERE userID = 'BBK';
SELECT * FROM buyTBL; --BBK削除の確認
ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL (userID);
--BuytBLの4番目のデータを再入力
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「BBK」、「ディスプレイ」、「電子」、200,5)を購入する.

--오류발생.
--ORA-02291: integrity constraint (TABLEDB.FK_USERTBL_BUYTBL) violated - parent key not found

--外部キー制約の有効化
ALTER TABLE buyTBL
DISABLE CONSTRAINT FK_userTBL_buyTBL;
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「BBK」、「ディスプレイ」、「電子」、200,5)を購入する.
INSERT INTOはTBL VALuES(idSEQ.NEXTVAL、「KBS」、「ジーンズ」、「服装」、50、3)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「BBK」、「メモリ」、「電子」、50、10)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「SSK」、「書籍」、15,5)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL,“EJW”,“書籍”,15,2)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「EJW」、「ジーンズ」、「服装」、50、1)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「BBK」、「スニーカー」、NULL、30、2)を購入した.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「EJW」、「Book」、「書籍」、15、1)を購入する.
INSERT INTOはTBL VALUES(idSEQ.NEXTVAL、「BBK」、「スニーカー」、NULL、30、2)を購入した.
ALTER TABLE buyTBL
ENABLE NOVALIDATE CONSTRAINT FK_userTBL_buyTBL;
--CHECK制約をuserTBLの生年月日のみを1900～2017に設定する
ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK(birthYear>=1900 ANDbirthYear<=2017)--CHECK制約の設定
ENABLE;

--오류 발생
--ORA-02293: cannot validate (TABLEDB.CK_BIRTHYEAR) - check constraint violated
--02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
--*Cause:    an alter table operation tried to validate a check constraint to
--           populated table that had nocomplying values.
--*Action:   Obvious

ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK(birthYear>=1900 ANDbirthYear<=2017)--CHECK制約の設定
ENABLE NOVALIDATE; --NOVALLIDATEの追加
--残りのuserTBLデータを入力
INSERT INTO USERTBL VALES(「SSK」、「成時京」、1979、「ソウル」、NULL、NULL、186、「2013-12-12」)
INSERT INTO USERTBL VALUES(「LJB」,「任宰範」,1963,「ソウル」,「016」,「666666」,182,「2009-9」);
INSERT INTO USERTBL VALES(「YJS」、「尹鐘信」、1969、「慶尚南道」、NULL、NULL、170、「2005-5-5」)
INSERT INTO USERTBL VALES(EJIW、殷志源、1972、慶尚北道、011、88888888、174、2014-3-3);
INSERT INTO USERTBL VALUES(「JKW」,「趙関羽」,「試合」,「018」,「99999999」,「172」,「2010-10」)
INSERT INTO USERTBL VALES(「BBK」,「BobbyKim」,1973,「ソウル」,「010」,「00000000」,「176」,「2013-5-5」)
--バービー・キング(BBK)が会員を脱退した場合(=会員表から削除)購入した記録も削除されますか
DELETE FROM userTBL WHERE userID = 'BBK';
--エラー
--ORA-02292: integrity constraint (TABLEDB.FK_USERTBL_BUYTBL) violated - child record found
ALTER TABLE buyTBL
DROP CONSTRAINT FK_userTBL_buyTBL;
ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL (userID)
ON DELETE CASCADE; --ON DELETE CASCAADE文を一緒に設定
--BuytBLに基づいて削除されたか
DELETE FROM userTBL WHERE userID = 'BBK';
SELECT * FROM buyTBL;
--userTBLからCHECK制約を含む生年月日(birthYear)列を削除します.
ALTER TABLE userTBL-ALTER TABLEに削除
DROP COLUMN birthYear;
SELECT * FROM userTBL;
SELECT birthYear FROM userTBL;