CREATE TABLE contents(
c_id INT PRIMARY KEY AUTO_INCREMENT ,
c_name VARCHAR(100) NOT NULL UNIQUE);

CREATE TABLE itinerary(
i_id INT PRIMARY KEY AUTO_INCREMENT,
i_name VARCHAR(100) NOT NULL UNIQUE,
price INT NOT NULL,
TIME DATE,
c_id INT,
FOREIGN KEY (c_id) REFERENCES contents(c_id));
ALTER TABLE itinerary MODIFY price DOUBLE(8,2) NOT NULL;
ALTER TABLE itinerary ADD CONSTRAINT itinerary FOREIGN KEY (c_id) REFERENCES contents(c_id) ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE itinerary DROP FOREIGN KEY itinerary_ibfk_1;
SHOW CREATE TABLE itinerary;
ALTER TABLE itinerary DROP FOREIGN KEY itinerary;



CREATE TABLE u(
u_id INT PRIMARY KEY AUTO_INCREMENT,
u_name VARCHAR(100) NOT NULL UNIQUE,
pass VARCHAR(10) NOT NULL,
NAME VARCHAR(10) NOT NULL,
birthday DATE,
sex VARCHAR(1) NOT NULL,
telephonenumber VARCHAR(11) NOT NULL,
email VARCHAR(32)
);

CREATE TABLE favorite(
i_id INT,
u_id INT,
f_time DATE,
PRIMARY KEY(i_id,u_id),
FOREIGN KEY(i_id) REFERENCES itinerary(i_id),
FOREIGN KEY(u_id) REFERENCES u(u_id)
);
ALTER TABLE favorite ADD CONSTRAINT favorite FOREIGN KEY (i_id) REFERENCES itinerary(i_id)  ON UPDATE CASCADE
ON DELETE CASCADE;
-- alter table favorite add constraint favorite foreign key (u_id) references u(u_id) on update cascade on
-- delete cascade;
DESC favorite;
SHOW TABLES;

CREATE TABLE contents(
c_id INT PRIMARY KEY AUTO_INCREMENT ,
c_name VARCHAR(100) NOT NULL UNIQUE);

CREATE TABLE itinerary(
i_id INT PRIMARY KEY AUTO_INCREMENT,
i_name VARCHAR(100) NOT NULL UNIQUE,
price INT NOT NULL,
TIME DATE,
c_id INT,
FOREIGN KEY (c_id) REFERENCES contents(c_id));
ALTER TABLE itinerary MODIFY price DOUBLE(8,2) NOT NULL;
ALTER TABLE itinerary ADD CONSTRAINT itinerary FOREIGN KEY (c_id) REFERENCES contents(c_id) ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE itinerary DROP FOREIGN KEY itinerary_ibfk_1;
SHOW CREATE TABLE itinerary;
ALTER TABLE itinerary DROP FOREIGN KEY itinerary;



CREATE TABLE u(
u_id INT PRIMARY KEY AUTO_INCREMENT,
u_name VARCHAR(100) NOT NULL UNIQUE,
pass VARCHAR(10) NOT NULL,
NAME VARCHAR(10) NOT NULL,
birthday DATE,
sex VARCHAR(1) NOT NULL,
telephonenumber VARCHAR(11) NOT NULL,
email VARCHAR(32)
);

CREATE TABLE favorite(
i_id INT,
u_id INT,
f_time DATE,
PRIMARY KEY(i_id,u_id),
FOREIGN KEY(i_id) REFERENCES itinerary(i_id),
FOREIGN KEY(u_id) REFERENCES u(u_id)
);
ALTER TABLE favorite ADD CONSTRAINT favorite FOREIGN KEY (i_id) REFERENCES itinerary(i_id)  ON UPDATE CASCADE
ON DELETE CASCADE;
-- alter table favorite add constraint favorite foreign key (u_id) references u(u_id) on update cascade on
-- delete cascade;
DESC favorite;
SHOW TABLES;