hr_schema_install

hr_schema_install - SET SERVEROUTPUT ON SIZE UNLIMITED;...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: SET SERVEROUTPUT ON SIZE UNLIMITED; BEGIN FOR rec IN (SELECT table_name, constraint_name FROM user_constraints WHERE table_name IN ('EMPLOYEES' , 'DEPARTMENTS' , 'JOBS' , 'COUNTRIES' , 'REGIONS' , 'LOCATIONS' , 'JOB_HISTORY') AND constraint_type IN ('R')) LOOP BEGIN DBMS_OUTPUT.put_line( 'alter table ' || rec.table_name || ' ' || 'disable constraint ' || rec.constraint_name); EXECUTE IMMEDIATE 'alter table ' || rec.table_name || ' ' || 'disable constraint ' || rec.constraint_name; DBMS_OUTPUT.put_line( 'alter table ' || rec.table_name || ' ' || 'drop constraint ' || rec.constraint_name); EXECUTE IMMEDIATE 'alter table ' || rec.table_name || ' ' || 'drop constraint ' || rec.constraint_name; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END; END LOOP; END set_constraints; / DROP TABLE job_history / DROP TABLE employees / DROP TABLE departments / DROP TABLE regions / DROP TABLE countries / DROP TABLE locations / DROP TABLE jobs / CREATE TABLE regions ( region_id NUMBER CONSTRAINT region_id_nn NOT NULL , region_name VARCHAR2 (25) ) / CREATE TABLE countries ( country_id CHAR (2) CONSTRAINT country_id_nn NOT NULL , country_name VARCHAR2 (40) , region_id NUMBER , CONSTRAINT country_c_id_pk PRIMARY KEY (country_id) ) / COMMENT ON TABLE countries IS 'country table. Contains 25 rows. References with locations table.' / COMMENT ON COLUMN countries.country_id IS 'Primary key of countries table.' / COMMENT ON COLUMN countries.country_name IS 'Country name' / COMMENT ON COLUMN countries.region_id IS 'Region ID for the country. Foreign key to region_id column in the departments table.' / CREATE TABLE locations ( location_id NUMBER (4) , street_address VARCHAR2 (40) , postal_code VARCHAR2 (12) , city VARCHAR2 (30) CONSTRAINT loc_city_nn NOT NULL , state_province VARCHAR2 (25) , country_id CHAR (2) ) / COMMENT ON TABLE locations IS 'Locations table that contains specific address of a specific office, warehouse, and/or production site of a company. Does not store addresses / locations of customers. Contains 23 rows; references with the departments and countries tables. ' / COMMENT ON COLUMN locations.location_id IS 'Primary key of locations table' / COMMENT ON COLUMN locations.street_address IS 'Street address of an office, warehouse, or production site of a company. Contains building number and street name' / COMMENT ON COLUMN locations.postal_code IS 'Postal code of the location of an office, warehouse, or production site of a company. ' / COMMENT ON COLUMN locations.city IS 'A not null column that shows city where an office, warehouse, or production site of a company is located. ' / COMMENT ON COLUMN locations.state_province IS 'State or Province where an office, warehouse, or production site of a company is located.' / COMMENT ON COLUMN locations.country_id IS 'Country where an office, warehouse, or production site of a company is located. Foreign key to country_id column of the countries table.' / CREATE TABLE departments ( department_id NUMBER (4)...
View Full Document

Page1 / 74

hr_schema_install - SET SERVEROUTPUT ON SIZE UNLIMITED;...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online