Check constrain with 2 tables

Question

I have two tables: beds and room and I want to make sure that on insert each room can have a maximum of 5 beds (i.e. the company owns 50 beds in total which can get swapped from room to room in various combinations but each room can only have 5 at the most).

The primary key in beds is a composite key of room_id and bed_id (room_id being the foreign key) but I'm not sure how to implement this as a constraint for the table?

TABLES BELOW

CREATE TABLE "ROOM"
   ("ROOM_NO" NUMBER(*,0), 
    "ROOM_NAME" VARCHAR2(45 CHAR) NOT NULL ENABLE, 
     CONSTRAINT "ROOM_NO_PK" PRIMARY KEY ("ROOM_NO")



CREATE TABLE "BED" 
   ("ROOM_NO" NUMBER(*,0), 
    "BED_NO" NUMBER(*,0), 
     CONSTRAINT "BED_PK" PRIMARY KEY ("ROOM_NO", "BED_NO")

I'm new to sql and have not covered triggers and sequences please can you explain in simple terms how to do this?

or have it controlled from the application over a global threshold — RoMEoMusTDiE, Mar 16 '17 at 01:38
Check out this answer http://stackoverflow.com/questions/8770552/can-i-have-a-constraint-on-count-of-distinct-values-in-a-column-in-sql, which references this blog post http://tonyandrews.blogspot.com/search/label/constraints — user681574, Mar 16 '17 at 04:23
I agree with @GordonLinoff this can be solved with triggers. If you want help with trigger solution thought you should provide some sample table structures, data and more clearly describe expected behaviour. — PKey, Mar 16 '17 at 07:59
@Plirkee A hotel has a store of 50 beds numbered from 1 to 50 but I want to ensure that a room can have any number of beds from the store. numbered start at 1 to a maximum of 5. So room 1 could have bed 1, bed, 2 then room 2 would have bed 1 bed 2 bed 3 bed 4 so there bed 1 in room 1 and bed 1 in room 2 should be different beds (I hope this makes sense. — user3185534, Mar 16 '17 at 11:59
@Plirkee A hotel has a store of 50 beds numbered from 1 to 50 but I want to ensure that a room can have any number of beds from the store, numbers start at 1 to a maximum of 5. So room 1 could have bed 1, bed, 2 then room 2 could have bed 1 bed 2 bed 3 bed 4 so bed 1 in room 1 and bed 1 in room 2 should be different beds (I hope this makes sense) — user3185534, Mar 16 '17 at 12:05
Now that I had a second look at it, it might be not that simple. as, if one would like to put a `trigger` on `bed` table - one would be facing `mutating trigger` problem (trigger trying to access data of a table that is being updated)...wonder what @GordonLinoff thinks about it. — PKey, Mar 16 '17 at 12:48

PKey · Accepted Answer · 2017-03-16T20:22:51.337

0

Ok , based on your comment I'll modify the approach a bit

Here we go :

    CREATE TABLE "BED" 
   (
    "BED_NO" NUMBER(*,0), 
    "ROOM_NO" NUMBER(*,0), 
    "BED_ROOM_SN" NUMBER(*,0),   

     CONSTRAINT "BED_PK" PRIMARY KEY ("BED_NO"),
     CONSTRAINT rm_fk FOREIGN KEY ("ROOM_NO") REFERENCES "ROOM",
     CONSTRAINT chk_beds CHECK ("BED_ROOM_SN" IN (1,2,3,4,5)),
     CONSTRAINT constraint_name UNIQUE ("ROOM_NO", "BED_ROOM_SN")
    )

And I'll explain:

BED table contains:

BED_NO (bed serial number, also a key in this table ... in your case - numbers 1..50)
ROOM_NO - room number (a foreign key, referencing ROOM table)
BED_ROOM_SN - SN of the bed inside a room, that could only be from 1 to 5
Also, a room can have only one first bed, one second bed etc - hence unique (Room_no,BED_ROOM_SN)

Regarding normalization, here we have one-to-many relation (one room has many beds, however one bed goes only into one room), so that's why room's key goes as foreign key to bed table.

edited Mar 16 '17 at 20:22

answered Mar 16 '17 at 13:04

PKey

3,715
1
14
39

That's the table structure I went with originally but the question specifically asks for bed and room to be in separate table after normalisation. – user3185534 Mar 16 '17 at 13:11
@user3185534 did you mention that in your question? cause I am using tables given in your question and just adding an extra constrain. I am not following ... what exactly do you mean 'bed and room to be separate after normalisation'? – PKey Mar 16 '17 at 13:17
thanks for the help..this seems so solve the problem. – user3185534 Mar 17 '17 at 14:11

Check constrain with 2 tables

1 Answers1