|











|
|
|
 |
0 |
|
View Articles |
|
 |
 Name |
¼Õº´¸ñ |
(2002-05-07 23:47:45, Hit : 18944, Vote : 2353)
|
|
 |
Homepage |
 http://www.itmembers.net |
 |
Subject |
 DDL(Data Definition Language) - CREATE TABLE(ß¾) |
 |
 |
À̹ø ½Ã°£ºÎÅÍ´Â µ¥ÀÌÅÍ Á¤ÀǾ ´ëÇØ ¾Ë¾Æº¸°Ú½À´Ï´Ù.
µ¥ÀÌÅÍ Á¤ÀǾî(DDL)´Â Å×À̺íÀ» »ý¼ºÇÏ´Â CREATE, Å×À̺íÀÇ ±¸Á¶¸¦ º¯°æÇÏ´Â ALTER, Å×À̺íÀ» »èÁ¦ÇÏ´Â DROP ¸í·É µîÀÌ ÀÖ½À´Ï´Ù.
µÎ ½Ã°£¿¡ °ÉÃÄ CREATE¿¡ ´ëÇØ »ìÆìº¸°Ú½À´Ï´Ù.

CREATE ¸í·ÉÀº ±¸Ã¼ÀûÀ¸·Î ¼³¸íÇÑ ÀûÀº ¾øÁö¸¸ Áö±Ý±îÁö ²Ï ¸¹ÀÌ ½Ç½ÀÀ» ÇØ¿Ô½À´Ï´Ù.
¿¹Á¦ Å×À̺íÀ» ¸¸µé±â À§ÇØ CREATE ¹®À» »ç¿ëÇß½À´Ï´Ù. ´Ù¸¸ ±¸Ã¼ÀûÀ¸·Î ¼³¸í¸¸ ¾ÈÇßÀ» »ÓÀÌÁÒ.
CREATE ¹®Àº ¾î¶»°Ô ±¸¼ºµÇ¾î ÀÖ´ÂÁö Çѹø »ìÆìº¼±î¿ä?
- CREATE ¹® Çü½Ä
CREATE TABLE table_name
(column_one data_type [constraint, DEFAULT expression],
column_two data_type [constraint, DEFAULT expression],
¡¦,
CONSTRAINT constraint_name UNIQUE(column),
CONSTRAINT constraint_name PRIMARY KEY(column),
CONSTRAINT constraint_name FOREIGN KEY(column1)
REFERENCES table_name(column2),
CONSTRAINT constraint_name CHECK(column expr),
);
¿ì¼± ´«¿¡ ¶ç´Â °ÍµéÀÌ ¸î °³ º¸ÀÌÁÒ?
CONSTRAINT, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, CHECK µî
¸ÕÀú CONSTRAINT´Â µ¥ÀÌÅÍÀÇ ¹«°á¼º(Integrity)À» À¯ÁöÇϱâ À§ÇÏ¿© »ç¿ëÀÚ°¡ ÁöÁ¤ÇÏ´Â Á¦¾à Á¶°ÇÀ» ÀǹÌÇÕ´Ï´Ù.
¹«°á¼º¿¡ ´ëÇØ¼´Â ÀÌ¹Ì 2, 3, 12, 14°¿¡¼ µÎ·ç ¾ð±ÞµÇ¾ú´ø ³»¿ëÀÌ´Ï ¿©±â¼´Â µû·Î ¼³¸íÇÏÁö ¾Ê°Ú½À´Ï´Ù.
CONSTRAINT¸¦ Á¤ÀÇÇÏ´Â ¹æ¹ýÀº µÎ °¡Áö°¡ ÀÖ½À´Ï´Ù.
Ä÷³º°·Î Á¦¾à Á¶°ÇÀ» ÁöÁ¤ÇÏ´Â °ÍÀÌ ÀÖ°í, Composite Ä÷³¿¡ ´ëÇØ Á¦¾à Á¶°ÇÀ» Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù.(Composite Ä÷³¿¡ ´ëÇØ¼´Â Á¦13°¿¡¼ ¼³¸íÇÏ¿´½À´Ï´Ù.)
- Ä÷³º°·Î Á¦¾à Á¶°ÇÀ» ÁöÁ¤ÇÒ ¶§´Â ¾î¶°ÇÑ Á¦¾à Á¶°Çµµ »ç¿ë °¡´ÉÇÕ´Ï´Ù.
- ±×·¯³ª Composite Ä÷³¿¡ ´ëÇØ¼´Â NOT NULLÀ» »ç¿ëÇÒ ¼ö ¾ø½À´Ï´Ù.
±×·³, ±¸Ã¼ÀûÀ¸·Î Á¦¾à Á¶°Ç(CONSTRAINT)¿¡´Â ¾î¶² °ÍµéÀÌ ÀÖÀ»±î¿ä?
ÀÌ¹Ì ¸¹ÀÌ º¸¾Æ¿Â °ÍµéÀÌ ±×¸® ³¸¼³Áö´Â ¾ÊÀ» °ÍÀÔ´Ï´Ù.
- CONSTRAINTÀÇ Á¾·ù
ÀÌ¹Ì ÀÌ °Á ¾Õ ºÎºÐ¿¡¼ ¸¹ÀÌ ¾ð±ÞµÈ °ÍµéÀ̶ó Àǹ̴ ´ëºÎºÐ ÀÌÇØÇϼÌÀ» °Å¶ó ¹Ï°í ÁÖ¿äÇÑ ¸î °¡Áö¸¸ ¤°í ³Ñ¾î°¡°Ú½À´Ï´Ù.
- NOT NULL
NOT NULL Constraint°¡ ÁöÁ¤µÈ Ä÷³Àº NULL °ªÀ» °¡Áú ¼ö ¾ø½À´Ï´Ù.
±×¸®°í Ä÷³ ´ÜÀ§·Î Constraint¸¦ ÁöÁ¤ÇÒ ¶§¸¸ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.(Composite Ä÷³¿¡´Â »ç¿ëÇÒ ¼ö ¾ø½À´Ï´Ù.)
- UNIQUE
¿¹Àü¿¡ ERD ¼³¸íÇÏ¸é¼ ¾ð±ÞÇÑ ÀûÀÌ ÀÖÁÒ? Unique´Â ¸» ±×´ë·Î Áߺ¹µÇÁö ¾Ê¾Æ¾ß ÇÑ´Ù´Â ¼Ó¼ºÀÔ´Ï´Ù.
´ÜÀÏ Ä÷³À̳ª Composite Ä÷³¿¡ Unique ¼Ó¼ºÀ» ºÎ¿©ÇÒ ¶§ »ç¿ëÇϰí, Unique·Î ÁöÁ¤µÇ¸é Unique Index°¡ ÀÚµ¿À¸·Î »ý¼ºµË´Ï´Ù.(À妽º´Â ³ªÁß¿¡ ´Ù·ì´Ï´Ù.)
UNIQUE Ä÷³¿¡´Â NULL °ªÀÌ ¹Ýµå½Ã ¾ø¾î¾ßµÉ °Í °°Áö¸¸, Composite Ä÷³ ÁßÀÇ ÀϺΠÄ÷³¿¡´Â NULL °ªÀÌ µé¾î°¥ ¼ö ÀÖ´Â °æ¿ìµµ ÀÖ½À´Ï´Ù.
- PRIMARY KEY
Å×ÀÌºí¿¡¼ PRIMARY KEY´Â ´Ü Çϳª¸¸ ÀÖ¾î¾ß ÇÕ´Ï´Ù.
±×¸®°í ´ç¿¬È÷ NULL °ªÀº Çã¿ëÇÏÁö ¾Ê½À´Ï´Ù.
UNIQUE·Î ÁöÁ¤ÇßÀ» ¶§¿Í ¸¶Âù°¡Áö·Î Unique Index°¡ ÀÚµ¿À¸·Î »ý¼ºµË´Ï´Ù.
- FOREIGN KEY
Primary Key, Foreign Key¿¡ ´ëÇØ¼´Â Á¦13° °ü°èÇü µ¥ÀÌÅͺ£À̽º(2)-Table Keys¿¡¼ ÀÚ¼¼È÷ ´Ù·ç°í ÀÖ½À´Ï´Ù.
ÀÚ½Ä Å×À̺í(Child Table)¿¡¼ Á¤ÀÇÇÏ´Â ConstraintÀ̰í, ºÎ¸ð Å×À̺í(Parent Table)ÀÇ °ª°ú ÀÏÄ¡Çϰųª NULL °ªÀ̾î¾ß ÇÕ´Ï´Ù.
FOREIGN KEY·Î ÂüÁ¶ÇϰíÀÚ ÇÏ´Â Å×À̺íÀÌ ¸ÕÀú »ý¼ºµÇ¾î ÀÖ¾î¾ß ÇÕ´Ï´Ù.
ON DELETE CASCADE OptionÀ» ÁÖ¸é Cascade Delete RI¸¦ Àû¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.(Cascade Delete RI¿¡ ´ëÇÑ ¼³¸íÀº Á¦14° °ü°èÇü µ¥ÀÌÅͺ£À̽º(3)-ÂüÁ¶¹«°á¼º¿¡¼ ÀÚ¼¼È÷ ¼³¸íÇϰí ÀÖ½À´Ï´Ù.)
- CHECK
°¢ ÇàÀÇ Ä÷³ÀÌ ¸¸Á·ÇØ¾ß ÇÏ´Â ±¸Ã¼ÀûÀÎ Á¶°ÇÀ» Á¤ÀÇÇÏ´Â °÷ÀÔ´Ï´Ù.
- DEFAULT expression
Å×ÀÌºí¿¡ µ¥ÀÌÅ͸¦ ÀÔ·ÂÇÒ ¶§ ƯÁ¤ °ªÀ» ÁöÁ¤ÇÏÁö ¾ÊÀº °æ¿ì ±âº»ÀûÀ¸·Î ÀԷµǴ °ªÀ» ÁöÁ¤ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î ÇöÀçÀÇ ³¯Â¥¸¦ ÀÚµ¿À¸·Î »ðÀÔÇÏ·Á¸é SYSDATE¸¦ »ç¿ëÇÒ ¼ö ÀÖ°ÚÁÒ.
À̹ø ½Ã°£Àº ¿©±â±îÁö ¼³¸íÇϰí,
°è¼ÓÇØ¼ Å×À̺íÀ» Á÷Á¢ ¸¸µé¾î º¸±â·Î ÇϰڽÀ´Ï´Ù.
ÀÌ»ó µ¿ÁÖ ¾Æºü ¼Õº´¸ñÀ̾ú½À´Ï´Ù.
|
 |
 |
|
|
|