Mysql exclusive relation opportunity
Does mysql maintain an exclusive relationship? For example, I have 3 tables: CUSTOMER, FLAT, HOUSE. CUSTOMER belongs to FLAR xor HOUSE.
If there is an option in mysql to use one relationship for it? For example, CUSTOMER will have 2 fields, one associated with the object (FLAT or HOUSE), one with the stored identifier. I would like to check the db level that this relationship is not zero. Don't want to create foreign keys for every table (FLAT and HOUSE).
Thank.
added later: sorry for the wrong description. I don't want to create two different columns for each foreign key (for FLAT, HOUSE). I suggest something like an exclusive relationship and I create one column for id, which can be a foreign key to one of the FLAT or HOUSE tables
source to share
create table flat (
id int auto_increment,
primary key (id)
);
create table house (
id int auto_increment,
primary key (id)
);
create table customer (
id int auto_increment,
flat_id int,
house_id int,
primary key (id),
index flat_id (flat_id),
index house_id (house_id),
foreign key (flat_id) references flat(id) on delete cascade,
foreign key (house_id) references house(id) on delete cascade
);
source to share
I think it would be better to use a base parent table for my problem.
Thanks for George Cummins' comment. I can only create a DWELLING table with an id field.
CREATE TABLE DWELLING (
ID INT NOT NULL AUTO_INCREMENT
, PRIMARY KEY (ID)
);
And add the FK tables to the CUSTOMER, FLAT, HOUSE tables.
CREATE TABLE FLAT (
ID INT NOT NULL AUTO_INCREMENT
, DWELLING_ID INT NOT NULL
, PRIMARY KEY (ID)
, CONSTRAINT FK_DWELLING_FLAT FOREIGN KEY (DWELLING_ID) REFERENCES DWELLING (ID) ON DELETE cascade
);
CREATE TABLE HOUSE (
ID INT NOT NULL AUTO_INCREMENT
, DWELLING_ID INT NOT NULL
, PRIMARY KEY (ID)
, CONSTRAINT FK_DWELLING_HOUSE FOREIGN KEY (DWELLING_ID) REFERENCES DWELLING (ID) ON DELETE cascade
);
CREATE TABLE CUSTOMER (
ID INT NOT NULL AUTO_INCREMENT
, DWELLING_ID INT NOT NULL
, PRIMARY KEY (ID)
, CONSTRAINT FK_DWELLING_CUSTOMER FOREIGN KEY (DWELLING_ID) REFERENCES DWELLING (ID) ON DELETE cascade
);
After that, if I need to delete an apartment or house, I need to delete the associated DWELLING object.
I used this one http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql slide 29
If you disagree with me, please add a comment. I would be glad to see your comments.
Thank.
source to share