sql 실습

foods 테이블 생성

create table foods
(
	ID serial primary key,
	name varchar (100) UNIQUE
);

cars 테이블 생성

create table cars
(
	ID serial primary key,
	name varchar (100) UNIQUE
);

address 테이블 생성

create table address
(
	ID serial primary key,
	name varchar(20) NOT NULL,
	birthday date,
	hphone varchar(13) NOT NULL,
	memo varchar(100),
	food int,
	car int,

	CONSTRAINT fk_address_food
		FOREIGN KEY(food)
		REFERENCES foods(ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE,

	CONSTRAINT fk_address_car
		FOREIGN KEY(car)
		REFERENCES cars(ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

insert into foods (name) values ('짜장면');
insert into foods (name) values ('짬뽕');
insert into foods (name) values ('새우튀김');
insert into foods (name) values ('고기완자');

insert into cars (name) values ('sonata');
insert into cars (name) values ('benz');
insert into cars (name) values ('bmw');
insert into cars (name) values ('audi');
insert into cars (name) values ('tico');


insert into address (name, birthday, hphone, memo, food, car) values ('홍길동', '1987-01-05', '010-1111-1111', '절친', 1, 3);
insert into address (name, birthday, hphone, memo, food, car) values ('박길동', '2010-03-07', '010-1123-0515', '', 1, 1);
insert into address (name, birthday, hphone, memo, food, car) values ('최길동', '2000-01-01', '010-5515-4574', '절친', 3, 2);
insert into address (name, birthday, hphone, memo, food, car) values ('나길동', '1988-03-14', '010-7743-5743', '돈 빌림', 3, 3);
insert into address (name, birthday, hphone, memo, food, car) values ('윤지원', '1996-04-22', '010-7457-6475', '', 4, 3);
insert into address (name, birthday, hphone, memo, food, car) values ('고길동', '2007-09-09', '010-3511-5858', '', 4, NULL);
insert into address (name, birthday, hphone, memo, food) values ('박길동', '1997-05-05', '010-4874-7871', '절친 아님', 2);