MySQL Table 생성, FK(외래키) 설정

📜 MySQL Table 생성 및 외래키 설정

🟦 schema 생성

1. MySQL Connections 생성
   
   MySQL Connections 우측의 + 버튼을 눌러 Connection을 생성한다.

2. MySQL Workbench를 root권한으로 접속한다.

3. 좌측 상단에 Create a new schema in the connected server를 클릭한 후 스키마(DataBase)의 이름 입력 및 Charset/Collation 선택 후 Apply
   

4. Default Schema로 설정
   

🟦 Table 생성

create table 테이블명(
attribute명 data type,

attribute명 data type NOT NULL, // NULL값 허용 X

attribute명 data type UNIQUE, // 유일값 제약, 중복값 저장 불가

attribute명 data type DEFAULT 기본값, // 기본값 설정

// 주키 설정
[CONSTRAINT 제약조건이름]
PRIMARY KEY (attribute명)
or
attribute명 data type PRIMARY KEY

// 외래키 설정
[CONSTRAINT 제약조건이름]
FOREIGN KEY (attribute명)
REFERENCES 테이블명 (attribute명)

);

🟦 예시 ERD & 테이블 생성

create table Apartment_info(
apartment_id INT NOT NULL auto_increment PRIMARY KEY,
apartment_name VARCHAR(10) NOT NULL
);

create table Apartment_Notice_info(
apartment_notice_id INT NOT NULL auto_increment PRIMARY KEY,
apartment_id INT,
aprtment_notice_date DATETIME,
CONSTRAINT Apartment_Notice_contact_id_fk
FOREIGN KEY (apartment_id)
REFERENCES Apartment_info (apartment_id)
);

create table Device_info(
device_id INT NOT NULL auto_increment PRIMARY KEY
);

create table Member_info(
member_id INT NOT NULL auto_increment PRIMARY KEY,
apartment_id INT,
device_id INT,
name VARCHAR(12),
phone_number VARCHAR(11),
Email VARCHAR(30),
password VARCHAR(16),
address VARCHAR(30),
number_of_complaints INT, 
CONSTRAINT Member_contact_id_fk
FOREIGN KEY (apartment_id)
REFERENCES Apartment_info (apartment_id),
FOREIGN KEY (device_id)
REFERENCES Device_info (device_id)
);

create table Complaint_info(
complaint_id INT NOT NULL auto_increment PRIMARY KEY,
member_id INT,
complaint_contents TEXT,
complaint_date DATETIME,
CONSTRAINT Complaint_contact_id_fk
FOREIGN KEY (member_id)
REFERENCES Member_info (member_id)
);

create table Vehicle_info(
vehicle_id INT NOT NULL auto_increment PRIMARY KEY,
member_id INT,
device_id INT,
vehicle_number VARCHAR(10),
vehicle_model VARCHAR(12),
vehicle_color VARCHAR(8),
vehicle_departuretime DATETIME,
no_departure BOOLEAN,
CONSTRAINT Vehicle_contact_id_fk
FOREIGN KEY (member_id)
REFERENCES Member_info (member_id),
FOREIGN KEY (device_id)
REFERENCES Device_info (device_id)
);

create table Near_Device_info(
near_device_id INT NOT NULL auto_increment PRIMARY KEY,
device_id INT,
CONSTRAINT Device_contact_id_fk
FOREIGN KEY (device_id)
REFERENCES Device_info (device_id)
);

create table Near_Vehicle_info(
near_vehicle_id INT NOT NULL auto_increment PRIMARY KEY,
near_device_id INT,
device_id INT,
near_vehicle_number VARCHAR(10),
near_vehicle_model VARCHAR(12),
near_vehicle_color VARCHAR(8),
near_vehicle_departuretime DATETIME,
no_departure BOOLEAN,
CONSTRAINT Near_Vehicle_contact_id_fk
FOREIGN KEY (near_device_id)
REFERENCES Near_Device_info (near_device_id),
FOREIGN KEY (device_id)
REFERENCES Device_info (device_id)
);