Last active
August 29, 2015 13:57
-
-
Save joyeecheung/9617302 to your computer and use it in GitHub Desktop.
Script for adding key constrainst for TPC-H.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Sccsid: @(#)dss.ri 2.1.8.1 | |
-- tpch Benchmark Version 8.0 | |
USE tpch; | |
-- ALTER TABLE tpch.REGION DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.NATION DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.PART DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.SUPPLIER DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.PARTSUPP DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.ORDERS DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.LINEITEM DROP PRIMARY KEY; | |
-- ALTER TABLE tpch.CUSTOMER DROP PRIMARY KEY; | |
-- For table REGION | |
ALTER TABLE tpch.REGION | |
ADD PRIMARY KEY (R_REGIONKEY); | |
-- For table NATION | |
ALTER TABLE tpch.NATION | |
ADD PRIMARY KEY (N_NATIONKEY); | |
ALTER TABLE tpch.NATION | |
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references | |
tpch.REGION(R_REGIONKEY); | |
COMMIT WORK; | |
-- For table PART | |
ALTER TABLE tpch.PART | |
ADD PRIMARY KEY (P_PARTKEY); | |
COMMIT WORK; | |
-- For table SUPPLIER | |
ALTER TABLE tpch.SUPPLIER | |
ADD PRIMARY KEY (S_SUPPKEY); | |
ALTER TABLE tpch.SUPPLIER | |
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references | |
tpch.NATION(N_NATIONKEY); | |
COMMIT WORK; | |
-- For table PARTSUPP | |
ALTER TABLE tpch.PARTSUPP | |
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY); | |
COMMIT WORK; | |
-- For table CUSTOMER | |
ALTER TABLE tpch.CUSTOMER | |
ADD PRIMARY KEY (C_CUSTKEY); | |
ALTER TABLE tpch.CUSTOMER | |
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references | |
tpch.NATION(N_NATIONKEY); | |
COMMIT WORK; | |
-- For table LINEITEM | |
ALTER TABLE tpch.LINEITEM | |
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER); | |
COMMIT WORK; | |
-- For table ORDERS | |
ALTER TABLE tpch.ORDERS | |
ADD PRIMARY KEY (O_ORDERKEY); | |
COMMIT WORK; | |
-- For table PARTSUPP | |
ALTER TABLE tpch.PARTSUPP | |
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references | |
tpch.SUPPLIER(S_SUPPKEY); | |
COMMIT WORK; | |
ALTER TABLE tpch.PARTSUPP | |
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references | |
tpch.PART(P_PARTKEY); | |
COMMIT WORK; | |
-- For table ORDERS | |
ALTER TABLE tpch.ORDERS | |
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references | |
tpch.CUSTOMER(C_CUSTKEY); | |
COMMIT WORK; | |
-- For table LINEITEM | |
ALTER TABLE tpch.LINEITEM | |
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references | |
tpch.ORDERS(O_ORDERKEY); | |
COMMIT WORK; | |
ALTER TABLE tpch.LINEITEM | |
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references | |
tpch.PARTSUPP(PS_PARTKEY,PS_SUPPKEY); | |
COMMIT WORK; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment