[SQLite] primary key, autoincrement ๐Ÿ”‘

seunghyunยท2023๋…„ 12์›” 11์ผ
0

๐Ÿ’ป

๋ชฉ๋ก ๋ณด๊ธฐ
14/16

์ฐจ์ด์ , ์—ฐ๊ด€์„ฑ

SQLite์—์„œ AUTOINCREMENT์™€ PRIMARY KEY๋Š” ์„œ๋กœ ๋‹ค๋ฅธ ๊ฐœ๋…์ด์ง€๋งŒ, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ํ…Œ์ด๋ธ”์—์„œ ๊ณ ์œ  ์‹๋ณ„์ž๋ฅผ ์ƒ์„ฑํ•˜๋Š” ๋ฐ ํ•จ๊ป˜ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ด ๋‘ ๊ฐœ๋…์˜ ์ฃผ์š” ์ฐจ์ด์ ๊ณผ ์—ฐ๊ด€์„ฑ์„ ์‚ดํŽด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

๋จผ์ € AUTOINCREMENT ํ•„๋“œ๋Š” ์ฃผ๋กœ ๋ฐ์ดํ„ฐ์˜ ๊ณ ์œ ์„ฑ๊ณผ ์‹๋ณ„์„ฑ์„ ์œ ์ง€ํ•˜๋Š” ๋ฐ ์ค‘์ ์„ ๋‘๋ฉฐ, ์ด๋Š” ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ์„ ๋ณด์žฅํ•˜๋Š” ๋ฐ ์ค‘์š”ํ•ฉ๋‹ˆ๋‹ค.

CREATE TABLE example (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

PRIMARY KEY๋Š” ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ์„ ๋ณด์žฅํ•˜๊ธฐ ์œ„ํ•ด ๊ฐ ํ–‰์„ ๊ณ ์œ ํ•˜๊ฒŒ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋Š” ๋ฐ˜๋ฉด, AUTOINCREMENT๋Š” ๊ฐ’์ด ์ž๋™์œผ๋กœ ์ฆ๊ฐ€ํ•˜๋Š” ํŠน์ˆ˜ํ•œ ๋ฐฉ๋ฒ•์ž…๋‹ˆ๋‹ค.
AUTOINCREMENT๋Š” ์ฃผ๋กœ ๊ธฐ๋ณธ ํ‚ค์— ์ ์šฉ๋˜๋ฉฐ, ํ…Œ์ด๋ธ”์— ์ƒˆ๋กœ์šด ํ–‰์ด ์ถ”๊ฐ€๋  ๋•Œ๋งˆ๋‹ค ๊ณ ์œ ํ•œ ์ˆซ์ž๋ฅผ ์ž๋™์œผ๋กœ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.
SQLite์—์„œ๋Š” INTEGER PRIMARY KEY ์ปฌ๋Ÿผ์ด ์ž๋™์œผ๋กœ autoincrementing ์†์„ฑ์„ ๊ฐ–์Šต๋‹ˆ๋‹ค. ๋ช…์‹œ์ ์œผ๋กœ AUTOINCREMENT ํ‚ค์›Œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์ง€ ์•Š์•„๋„, SQLite๋Š” INTEGER PRIMARY KEY ์ปฌ๋Ÿผ์— ๋Œ€ํ•ด ์ž๋™ ์ฆ๊ฐ€ ๊ธฐ๋Šฅ์„ ์ œ๊ณตํ•ฉ๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ AUTOINCREMENT ํ‚ค์›Œ๋“œ๋ฅผ ๋ช…์‹œ์ ์œผ๋กœ ์ถ”๊ฐ€ํ•˜๋ฉด, SQLite๋Š” ์ˆซ์ž๋ฅผ ์žฌ์‚ฌ์šฉํ•˜์ง€ ์•Š๊ณ  ํ•ญ์ƒ ์ด์ „๋ณด๋‹ค ๋†’์€ ์ˆซ์ž๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.
PRIMARY KEY์™€ AUTOINCREMENT๋Š” ํ•จ๊ป˜ ์‚ฌ์šฉ๋  ๋•Œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ํ…Œ์ด๋ธ”์—์„œ ๊ฐ ํ–‰์„ ๊ณ ์œ ํ•˜๊ณ  ์ˆœ์ฐจ์ ์œผ๋กœ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ๋งค์šฐ ํšจ๊ณผ์ ์ž…๋‹ˆ๋‹ค.

์˜ˆ์‹œ

ํ…Œ์ด๋ธ” ์Šคํ‚ค๋งˆ ์˜ˆ์‹œ์ž…๋‹ˆ๋‹ค.

######### CREATE 'video', 'channel' table in videos.db #########
def create_tables_videosDB():
    with sqlite3.connect('videos.db') as connection:
        cursor = connection.cursor()
        # channel ํ…Œ์ด๋ธ”
        cursor.execute("""
            CREATE TABLE channel (
                cid INTEGER PRIMARY KEY AUTOINCREMENT, 
                channel_id TEXT
            )
        """)
        connection.commit()

        # video ํ…Œ์ด๋ธ”
        cursor.execute("""
            CREATE TABLE video (
                vid INTEGER PRIMARY KEY AUTOINCREMENT,
                cid INTEGER,
                video_id TEXT,
                title TEXT,
                link TEXT,
                FOREIGN KEY (cid) REFERENCES channel(cid)
            )
        """)
        connection.commit()

๊ทธ๋ฆฌ๊ณ  ์•„๋ž˜์˜ ์ฟผ๋ฆฌ๋Š” video ํ…Œ์ด๋ธ”๊ณผ channel ํ…Œ์ด๋ธ”์„ cid๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๊ฒฐํ•ฉํ•˜์—ฌ, ๋‘ ํ…Œ์ด๋ธ”์˜ ๊ด€๋ จ ๋ฐ์ดํ„ฐ๋ฅผ ํ•จ๊ป˜ ์กฐํšŒํ•  ์ˆ˜ ์žˆ๊ฒŒ ํ•ฉ๋‹ˆ๋‹ค. ์ด๋Ÿฌํ•œ ๋ฐฉ์‹์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ํ…Œ์ด๋ธ” ๊ฐ„์˜ ๊ด€๊ณ„๋ฅผ ์ •์˜ํ•˜๊ณ  ๋ฐ์ดํ„ฐ๋ฅผ ํšจ๊ณผ์ ์œผ๋กœ ๊ด€๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

SELECT *
FROM video
INNER JOIN channel ON video.cid = channel.cid;

์ฐธ๊ณ ๋กœ INSERT ์—ฐ์‚ฐ์—์„œ vid์™€ cid์™€ ๊ฐ™์€ AUTOINCREMENT ํ•„๋“œ๋Š” ์ง์ ‘ ์‚ฌ์šฉ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. INSERT ์—ฐ์‚ฐ์„ ํ•  ๋•Œ, ์ด๋Ÿฌํ•œ ํ•„๋“œ๋Š” ๋ณดํ†ต ์ƒ๋žต๋˜๋ฉฐ, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‹œ์Šคํ…œ์ด ์ž๋™์œผ๋กœ ๊ฐ’์„ ํ• ๋‹นํ•ฉ๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด, channel ํ…Œ์ด๋ธ”์— ์ƒˆ๋กœ์šด ๋ ˆ์ฝ”๋“œ๋ฅผ ์ถ”๊ฐ€ํ•  ๋•Œ๋Š” cid ๊ฐ’์„ ์ง€์ •ํ•˜์ง€ ์•Š๊ณ  channel_id๋งŒ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.

INSERT INTO channel (channel_id) VALUES ('channel123');

์ด ๊ฒฝ์šฐ, cid๋Š” ์ž๋™์œผ๋กœ ์ƒ์„ฑ๋˜๋Š” ๊ณ ์œ  ๋ฒˆํ˜ธ๋ฅผ ๋ฐ›๊ฒŒ ๋ฉ๋‹ˆ๋‹ค. AUTOINCREMENT ํ•„๋“œ๋Š” ์ฃผ๋กœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋‚ด๋ถ€์—์„œ ๊ฐ ๋ ˆ์ฝ”๋“œ๋ฅผ ์œ ์ผํ•˜๊ฒŒ ์‹๋ณ„ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋˜๋ฉฐ, ์‚ฌ์šฉ์ž๊ฐ€ ์ง์ ‘ ๊ด€๋ฆฌํ•  ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.

์‚ฌ์šฉ

AUTOINCREMENT ํ•„๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ์‚ฐ์—์„œ ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค:

  1. ์‚ฌ์šฉ๋˜๋Š” ์—ฐ์‚ฐ: SELECT, JOIN, DELETE
    ๊ฐ ๋ ˆ์ฝ”๋“œ์˜ ๊ณ ์œ  ์‹๋ณ„์ž๋กœ ์‚ฌ์šฉ๋˜์–ด, ๋ฐ์ดํ„ฐ ์กฐํšŒ๋‚˜ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”๊ณผ์˜ ๊ด€๊ณ„๋ฅผ ๋ช…ํ™•ํ•˜๊ฒŒ ํ•ฉ๋‹ˆ๋‹ค.

  2. ์‚ฌ์šฉ๋˜์ง€ ์•Š๋Š” ์—ฐ์‚ฐ: INSERT, UPDATE
    INSERT ์—ฐ์‚ฐ ์‹œ AUTOINCREMENT ํ•„๋“œ๋Š” ์ผ๋ฐ˜์ ์œผ๋กœ ์ƒ๋žต๋ฉ๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‹œ์Šคํ…œ์ด ์ž๋™์œผ๋กœ ๊ณ ์œ ๊ฐ’์„ ํ• ๋‹นํ•˜๋ฏ€๋กœ, ๊ฐœ๋ฐœ์ž๊ฐ€ ์ง์ ‘ ์ง€์ •ํ•  ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.

์ด๋Ÿฌํ•œ ๋ฐฉ์‹์€ ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ์„ ๋ณด์žฅํ•˜๊ณ  ๋ฐ์ดํ„ฐ ๊ด€๋ฆฌ๋ฅผ ๊ฐ„์†Œํ™”ํ•˜๋Š” ๋ฐ ๋„์›€์ด ๋ฉ๋‹ˆ๋‹ค. AUTOINCREMENT ํ•„๋“œ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ์ž๋™ ๊ด€๋ฆฌ๋˜๋ฏ€๋กœ, ์‚ฌ์šฉ์ž๋Š” ๋ ˆ์ฝ”๋“œ์˜ ๊ณ ์œ ์„ฑ์„ ์‹ ๊ฒฝ ์“ธ ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.


๐Ÿ”— Reference

profile
๐Ÿ‘ฉ๐Ÿปโ€๐Ÿ’ป๐ŸŽฎ

0๊ฐœ์˜ ๋Œ“๊ธ€