Untitled
unknown
plain_text
5 months ago
3.4 kB
8
Indexable
-- Drop tables if they exist, handle exceptions gracefully BEGIN EXECUTE IMMEDIATE 'DROP TABLE member_service CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE feedback CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE classes CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE service CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE admins CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE trainer CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE member CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE users CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / -- Recreate tables CREATE TABLE users ( user_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL, phone CHAR(10), username VARCHAR2(50) NOT NULL UNIQUE, password VARCHAR2(100) NOT NULL, email VARCHAR2(100), role VARCHAR2(20) ); CREATE TABLE trainer ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, user_id NUMBER NOT NULL, CONSTRAINT fk_trainer_user FOREIGN KEY (user_id) REFERENCES users(user_id) ); CREATE TABLE admins ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, user_id NUMBER NOT NULL, CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES users(user_id) ); CREATE TABLE member ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, user_id NUMBER NOT NULL, membership VARCHAR2(50), CONSTRAINT fk_member_user FOREIGN KEY (user_id) REFERENCES users(user_id) ); CREATE TABLE service ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL, amount NUMBER NOT NULL ); CREATE TABLE classes ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL, trainer_id NUMBER NOT NULL, day VARCHAR2(10), time VARCHAR2(10), capacity VARCHAR2(10), description VARCHAR2(100), CONSTRAINT fk_trainer FOREIGN KEY (trainer_id) REFERENCES trainer(id) ); CREATE TABLE feedback ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, member_id NUMBER NOT NULL, trainer_id NUMBER NOT NULL, feedback_message VARCHAR2(100) NOT NULL, CONSTRAINT fk_member_id FOREIGN KEY (member_id) REFERENCES member(id), CONSTRAINT fk_trainer_id FOREIGN KEY (trainer_id) REFERENCES trainer(id) ); CREATE TABLE member_service ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, member_id NUMBER NOT NULL, service_id NUMBER NOT NULL, CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES member(id), CONSTRAINT fk_service FOREIGN KEY (service_id) REFERENCES service(id) );
Editor is loading...
Leave a Comment