MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.6.9-MariaDB-1:10.6.9+maria~deb11


create table rooms (id INT(11) not null primary key auto_increment)
create table cars (id INT(11) not null primary key auto_increment)
CREATE TABLE `houses` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `room_id`    int(11)      NOT NULL,
  `car_id`     int(11)      NOT NULL UNIQUE,
  `name`       varchar(128) NOT NULL,
  `is_deleted` tinyint(1)            DEFAULT 0,
  `sell_at`    datetime         NULL DEFAULT NULL,
  `created_at` datetime     NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime     NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY   (`id`),
    KEY `room_id` (`room_id`),
  --  KEY `car_id` (`car_id`),   THIS WILL CAUSE AN ERROR BECAUSE OF THE UNIQUE
  CONSTRAINT `houses_ibfk_1`
    FOREIGN KEY (`room_id`)
    REFERENCES `rooms` (`id`),
  CONSTRAINT `houses_ibfk_2`
    FOREIGN KEY (`car_id`)
    REFERENCES `cars` (`id`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
);
show create table houses;


Table
Create Table


houses
CREATE TABLE `houses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) NOT NULL,
  `car_id` int(11) NOT NULL,
  `name` varchar(128) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT 0,
  `sell_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `car_id` (`car_id`),
  KEY `room_id` (`room_id`),
  CONSTRAINT `houses_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`),
  CONSTRAINT `houses_ibfk_2` FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


insert into cars values (null),(null),(null),(null);
select * from cars;

Records: 4  Duplicates: 0  Warnings: 0




id


1

2

3

4


insert into rooms values (null),(null),(null),(null);
select * from rooms;

Records: 4  Duplicates: 0  Warnings: 0




id


1

2

3

4


insert into houses (room_id, car_id, name) values 
(1, 1, 'test1'),
(2, 2, 'test2'),
(3, 3, 'test3');
select * from houses;

Records: 3  Duplicates: 0  Warnings: 0




id
room_id
car_id
name
is_deleted
sell_at
created_at
updated_at


1
1
1
test1
0
null
2023-08-16 00:47:15
2023-08-16 00:47:15

2
2
2
test2
0
null
2023-08-16 00:47:15
2023-08-16 00:47:15

3
3
3
test3
0
null
2023-08-16 00:47:15
2023-08-16 00:47:15


insert into houses (room_id, car_id, name) values (1, 3, 'test4')

Duplicate entry '3' for key 'car_id'

id	room_id	car_id	name	sell_at	created_at	updated_at
1	1	1	test1	null	2023-08-16 00:47:15	2023-08-16 00:47:15
2	2	2	test2	null	2023-08-16 00:47:15	2023-08-16 00:47:15
3	3	3	test3	null	2023-08-16 00:47:15	2023-08-16 00:47:15