Arwid ThornströmandGitHub
307346744b
* migrations for the roomtypes * rename files * updated types * Added new tabel interiors_roomtypes * remove old migrations * fixed bug * correct numbers
21 lines
897 B
SQL
21 lines
897 B
SQL
-- Create a table called interiors_roomtypes
|
|
-- This table should link the id in interiors table with a id from roomtypes table
|
|
CREATE TABLE interiors_roomtypes (
|
|
id SERIAL PRIMARY KEY,
|
|
interior_id INTEGER NOT NULL,
|
|
roomtype_id INTEGER NOT NULL,
|
|
|
|
-- Foreign key constraints with CASCADE DELETE
|
|
CONSTRAINT fk_interiors_roomtypes_interiors
|
|
FOREIGN KEY (interior_id) REFERENCES interiors(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_interiors_roomtypes_roomtype
|
|
FOREIGN KEY (roomtype_id) REFERENCES roomtypes(id) ON DELETE CASCADE,
|
|
|
|
-- Ensure unique combination of room and roomtype (prevent duplicates)
|
|
UNIQUE(interior_id, roomtype_id)
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_interiors_roomtypes_interior_id ON interiors_roomtypes(interior_id);
|
|
CREATE INDEX idx_interiors_roomtypes_roomtype_id ON interiors_roomtypes(roomtype_id);
|