수정사항이 많아서 기존에 Appointment User 테이블 새로 만들어 주실수 있을까요?
1.User 테이블 생성
CREATE TABLE USER(
id INT NOT NULL PRIMARY key,
pw varchar(20) NOT NULL,
userName VARCHAR(20) NOT NULL,
userType VARCHAR(20) CHECK(userType IN('WHEELCHAIR','COMPANION')),
location VARCHAR(20),
rate DOUBLE,
times INT DEFAULT 0
);
Appointment 테이블 생성
CREATE TABLE Appointment(
id INT NOT NULL primary key,
wheelchairId int,
companionId INT,
appointDate DATE NOT NULL,
location varchar(20) NOT NULL,
bill INT NOT NULL,
FOREIGN KEY (wheelchairId) REFERENCES USER (id)
ON DELETE set null
ON UPDATE CASCADE,
FOREIGN KEY (companionId) REFERENCES USER (id)
ON DELETE set null
ON UPDATE CASCADE
);
3.UserAppointment 테이블 생성 후 foreign key로 다대다 관계설정
CREATE TABLE UserAppointment(
userId INT ,
appointmentId INT,
FOREIGN KEY (userId) REFERENCES USER (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (appointmentId) REFERENCES Appointment (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
수정사항이 많아서 기존에 Appointment User 테이블 새로 만들어 주실수 있을까요?
1.User 테이블 생성 CREATE TABLE USER( id INT NOT NULL PRIMARY key, pw varchar(20) NOT NULL, userName VARCHAR(20) NOT NULL, userType VARCHAR(20) CHECK(userType IN('WHEELCHAIR','COMPANION')), location VARCHAR(20), rate DOUBLE, times INT DEFAULT 0 );
3.UserAppointment 테이블 생성 후 foreign key로 다대다 관계설정 CREATE TABLE UserAppointment( userId INT , appointmentId INT, FOREIGN KEY (userId) REFERENCES USER (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (appointmentId) REFERENCES Appointment (id) ON DELETE CASCADE ON UPDATE CASCADE );