DROP TABLE PATIENT;
DROP TABLE PLAYER;
DROP TABLE WARD CASCADE CONSTRAINTS;



--CREATE TABLES
CREATE TABLE WARD(
	WARDNO	  INTEGER NOT NULL,
	WARDNAME  VARCHAR2(20)	NOT NULL,
	PATIENTID VARCHAR2(20)	NOT NULL,
	PLAYERID  VARCHAR2(20) 	NOT NULL,
	NPCNAME	  VARCHAR2(20) 	NOT NULL,
	NUMPATIENTSINWARD INTEGER NOT NULL CONSTRAINT NUMPATIENTSINWARD_CHECK CHECK(NUMPATIENTSINWARD >= 0 AND NUMPATIENTSINWARD <= 10),	
CONSTRAINT WARD_PK      PRIMARY KEY(WARDNO));

CREATE TABLE PLAYER(
	PLAYERID     VARCHAR2(20)  NOT NULL,
	PLAYERLEVEL  INTEGER	   NOT NULL  CONSTRAINT PLAYERLEVEL_CHECK CHECK(PLAYERLEVEL  >= 0 AND PLAYERLEVEL <= 99),	USERNAME     VARCHAR2(20)  NOT NULL,
	PASSWORD     VARCHAR2(20)  NOT NULL,
	POINTS	     INTEGER	   NOT NULL,
	PLAYER_WARDNO	 INTEGER   NOT NULL,
	PLAYER_PATIENTID VARCHAR2(20) 	NOT NULL,
CONSTRAINT PLAYER_PK 	PRIMARY KEY(PLAYERID),
CONSTRAINT PLAYER_WARDNO_FK	FOREIGN KEY(PLAYER_WARDNO) 	REFERENCES WARD(WARDNO),
CONSTRAINT PLAYER_PATIENTID_FK FOREIGN KEY(PLAYER_PATIENTID) REFERENCES PATIENT(PATIENTID));
	

CREATE TABLE PATIENT(
	PATIENTID	   VARCHAR2(20)  NOT NULL,
	PATIENTNAME	   VARCHAR2(20)  NOT NULL,
	PATIENTSTATE	   VARCHAR2(20)  NOT NULL,
	PATIENTDESCRIPTOR  CHAR	         NOT NULL, 
	PATIENT_PLAYERID   VARCHAR2(20)  NOT NULL,
	PATIENT_WARDNO	   INTEGER	 NOT NULL,
CONSTRAINT PATIENT_PK PRIMARY KEY(PATIENTID),
CONSTRAINT PATIENT_PLAYERID_FK  FOREIGN KEY(PATIENT_PLAYERID) 	REFERENCES PLAYER(PLAYERID),
CONSTRAINT PATIENT_WARDNO_FK 	FOREIGN KEY(PATIENT_WARDNO) 	REFERENCES WARD(WARDNO));

INSERT INTO WARD VALUES(1, 'WARD A', 'PATIENTID HANS', 'PLAYERID DAN', 'NPCNAME HUGO', NUMPATIENTSINWARD_CQENCE.NEXTVAL);
INSERT INTO PLAYER VALUES ('PLAYERID DAN', 1, 'USERNAME GRAHAM', 'PASSWORD GRAHAM', 1, 1, 'PATIENTID HANS');
INSERT INTO PATIENT	VALUES('PATIENTID HANS', 'PATIENTNAME JAMES', 'SICK', 'R', 'PLAYERID DAN', 1);




CREATE VIEW VIEW_PLAYER
AS SELECT PLAYERID, PLAYERLEVEL, POINTS, WARDNO
FROM PLAYER;	

quote:

---

Originally posted by nigelrivett

Oracle?

Are you running this as a single script?
Maybe you need to create the tables first then add the FKs then run the inserts.
Your second table has an FK to the third table - it will fail because the third table doesn't exist.

---

quote:

---

Originally posted by nigelrivett

You say you're doing a project for college. Does it include anything about relational theory or relational database design?

---