Oracle SQL constraint to check data before allowing INSERT

Question

kinda new to SQL, so need some help here. Firstly, please refer to the picture for my tables (BED table, WARD table, PATIENT table).

I am using Oracle SQL Developer Version 19.4.0.354 as a learning tool.

=====================================================================

EDIT: So i have changed the tables as like below:

BED(BEDNO, BED_OCCUPIEDDATE, WARDNO, PATIENTNO)

PATIENTS(PATIENTNO, FIRSTNAME, LASTNAME, ADDRESS, GENDER, DATEOFBIRTH, MARITALSTATUS, PHONENO, REGISTEREDDATE, WAITINGLISTDATE, EXPECTEDSTAY, DATEWARDED, EXPECTEDLEAVE, DATELEFT)

WARD(WARDNO, WARDNAME, LOCATION, NUMBEROFBED, EXTNO)

BedNo is unique in the entire database. numbered from 1-240. Meaning WardA will have bedno 1-10, WardB might have bedno 11-20 and so on.

How would i be able to allow allocation of patient to a ward only if there are bed available in the ward? Because currently, beds are not "tagged" to the ward if you refer to my ERD.

Please don't post images of code, data, or table layouts. Instead, please include them as formatted text. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Mar 21, 2020 at 17:30
I saw this same question on OTN a few days ago .... community.oracle.com/thread/4321559 — EdStevens
– EdStevens, Commented Mar 21, 2020 at 18:01
Hi, thats me too. But probably this community is more appropriate. — user2930173
– user2930173, Commented Mar 22, 2020 at 1:42

sticky bit · Accepted Answer · 2020-03-21 17:45:38Z

1

Change the design a little.

First have the wards' table.

CREATE TABLE ward
             (wardno ...,
              ...
              PRIMARY KEY (wardno)
              ...);

Then in the beds' table reference the ward who attends this bed but not the patient that occupies it.

CREATE TABLE bed
             (bedno ...,
              wardno ...,
              ...
              PRIMARY KEY (bedno),
              FOREIGN KEY (wardno)
                          REFERENCES ward
                                     (wardno),
              ...);

In the patients' table reference the bed a patient occupies. A non existing bed cannot be referenced and without a bed a patient doesn't get a ward. By declaring the refereed bed unique, no more than one patient can occupy the same bed. If you don't want any patients without a bed, you can also declare the bed reference NOT NULL.

CREATE TABLE patient
             (patientno ...,
              ...
              bedno ... NOT NULL,
              PRIMARY KEY (patientno),
              FOREIGN KEY (bedno)
                          REFERENCES bed
                                     (bedno),
              UNIQUE (bedno),
              ...);

And by the way, you shouldn't materialize the number of beds a ward attends, if that is what ward.numberofbed is supposed to store. Instead you should query that figure when needed. For convenience you can also use a view, so you don't need to repeat such a query.

answered Mar 21, 2020 at 17:45

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2930173 Over a year ago

Okay, yea i will probably try your suggestion. But initially, all bed numbers are not unique. Meaning to say Ward A will have bed number 1-10. Ward B will have bed 1-12... and so on.

Collectives™ on Stack Overflow

Oracle SQL constraint to check data before allowing INSERT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related