찾으시는 정보가 있으시다면
주제별reference를 이용하시거나
우측 상단에 있는 검색기능을 이용해주세요

CONSTRAINT

Are used to specify rules for data in a table

Are used to limit the type of data that can go into a table

This ensures the accuracy and reliable of the data in the table

Can be specified when the table is created with the CREATE TABLE statement

Or after the table is created with the ALTER TABLE statement

CREATE

Can be column level or table level

Column level constraints apply to a column, and table level constraints apply to the whole table

-- column level
CREATE TABLE table_name(
column1 datatype constraint_type,
column2 datatype constraint_type,
…
);
-- table level
CREATE TABLE table_name(
-- NOT NULL only column level
column1 datatype NOT NULL,
column2 datatype,
column3 datatype,
-- table level
constraint_type (column_name),
-- to name a constraint
CONSTRAINT constraint_name constraint_type(column_name(s))
);

NOT NULL

By default, a column can hold NULL values

The NOT NULL CONSTRAINT enforces a column to NOT accept NULL values

This enforces a field to always contain a value, which means that you can’t insert a new record, or update a record without adding a value to this field

UNIQUE

Both the UNIQUE and PRIMARY KEY CONSTRAINTs provide a guarantee for uniqueness for a column or set of columns

PRIMARY KEY automatically has a UNIQUE

However you can have many UNIQUE per table, but only one PRIMARY KEY per table

PRIMARY KEY

It Uniquely identifies each record in a table

Must contain UNIQUE values, and can’t contain NULL values

A table can have only one primary key; and in the table, this primary key can consist of single or multiple columns(fields)

FOREIGN KEY

Is used to prevent actions that would destroy links between tables

Is a field (or collection of field) in one table, that refers to the PRIMARY KEY in another table

The table with the foreign key is called the child table, and the table with primary key is called the referenced or parent table

Is prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table

CREATE TABLE child_table(
column1 datatype FOREIGN KEY REFERENCES parent_table1(pk_column1)
);

-- Naming of a FOREIGN KEY
CREATE TABLE child_table(
column1 datatype,
CONSTRAINT fk_name FOREIGN KEY (column1)
REFERENCES parent_table(pk_column1)
);

CHECK

Is used to limit the value range that can be placed in a column

Allow only certain values for this column

It can limit the values in certain columns based on values in other columns in the row

CREATE TABLE table(
column1 datatype CHECK (condition)
);
-- Naming of a CHECK
CREATE TABLE table(
column1 datatype,
CONSTRAINT chk_name CHECK (condition)
);

DEFAULT

Is used to set a default value for a column

The default value will be added to all new records, if no other value is specified

CREATE TABLE table(
column datatype DEFAULT default_value
);