실습환경
mysql -V
mysql -u root -p
use zerobase;
create table tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ...)
);
1. 하나의 칼럼을 기본키로 설정하는 경우
create table person
(
pid int not null,
name varchar(16),
age int,
sex char,
PRIMARY KEY (pid)
);
desc person;
2. 여러 개의 칼럼을 기본키로 설정하는 경우
create table animal
(
name varchar(16) not null,
type varchar(16) not null,
age int,
PRIMARY KEY (name, type)
);
desc animal;
ALTER TABLE tablename
DROP PRIMARY KEY;
1. 하나의 칼럼이 기본키로 설정된 경우
alter table person
drop primary key;
desc person;
2. 여러 개의 칼럼이 기본키로 설정된 경우 (방법 동일)
alter table animal
drop primary key;
desc animal;
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...);
1. 하나의 칼럼을 기본키로 설정하는 경우
alter table person
add primary key (pid);
desc person;
2. 여러 개의 칼럼을 기본키로 설정하는 경우
alter table animal
add constraint PK_animal primary key (name, type);
desc animal;
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype,
column4 datatype,
...
CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ...),
CONSTRAINT constraint_name
FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);
1. CREATE TABLE에서 FOREIGN KEY를 지정하는 경우
create table orders
(
oid int not null,
order_no varchar(16),
pid int,
primary key (oid),
constraint FK_person foreign key (pid) references person(pid)
);
desc orders;
2. CREATE TABLE 에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT 를 생략할 수 있다.
create table job
(
jid int not null,
name varchar(16),
pid int,
primary key (jid),
foreign key (pid) references person(pid)
);
desc job;
SHOW CREATE TABLE tablename;
show create table job;
ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;
alter table orders
drop foreign key FK_person;
desc orders;
show create table orders;
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);
alter table orders
add foreign key (pid) references person(pid);
desc orders;
show create table orders;
police_station과 crime_status 테이블 사이에 관계 (Foreign Key)를 설정해 봅시다. AWS RDS(database-1)의 zerobase에서 작업합니다.
select count(distinct name) from police_station;
select count(distinct police_station) from crime_status;
select distinct name from police_station limit 3;
select distinct police_station from crime_status limit 3;
select c.police_station, p.name
from crime_status as c, police_station as p
where p.name like concat('서울', c.police_station, '경찰서')
group by c.police_station, p.name;
alter table police_station
add primary key (name);
desc police_station;
alter table crime_status
add column reference varchar(16);
desc crime_status;
alter table crime_status
add foreign key (reference) references police_station(name);
desc crime_status;
update crime_status as c, police_station as p
set c.reference = p.name
where p.name like concat('서울', c.police_station, '경찰서');
select distinct police_station, reference from crime_status;
select c.police_station, p.address
from crime_status as c, police_station as p
where c.reference = p.name
group by c.police_station;
create table study
(
study_id int not null,
study_date date,
study_time time,
patient_id int,
primary key (study_id),
constraint FK_study foreign key (patient_id) references person (pid)
);
desc study;
show create table study;
alter table study
drop primary key;
desc study;
alter table study
drop foreign key FK_study;
desc study;
show create study;
alter table study
add foreign key (patient_id) references person (pid);
show create study;
alter table study
add primary key (study_id);
desc study;