[PGS9.2] CONSTRAINT Management - ver.dbrang

/****************************************************************************************************************
-- Title : [PGS9.2] CONSTRAINT Management - ver.dbrang
-- Reference : www.postgresql.com
-- Key word : postgresql 제약조건 constraint primary key pk foreign key fk unique check cascade restrict default
                    디폴트 체크 유미트 케스케이드
****************************************************************************************************************/

/*

-- set default

*/

create table products (

    product_no integer,

    name text,

    price numeric default 9.99

); 

-- drop defalt
alter table products alter column price DROP DEFAULT;

/*

-- set check

*/

-- row check

create table products (

    product_no integer,

    name text,

    price numeric check (price> 0)

); 

create table products (

    product_no integer,

    name text,

    price numeric constraint positive_price check (price> 0)

); 

create table products (

    product_no integer,

    name text,

    price numeric check (price> 0)

    discounted_price numeric check (discounted_price> 0) 

                             check (price> discounted_price)

); 

-- table check

create table products (

    product_no integer,

    name text,

    price numeric,

    check (price> 0)

    discounted_price numeric,

    check (discounted_price> 0)

    check (price> discounted_price)

); 

create table products (

    product_no integer,

    name text,

    price numeric,

    check (price> 0)

    discounted_price numeric,

    check (discounted_price> 0)

    constraint valid_discount check (price> discounted_price)

); 

/*

-- set unique

*/

create table products (

    product_no integer unique,

    name text,

    price numeric

); 

create table example (

    a integer,

    b integer,

    c integer,

    unique (a, c)

);

create table products (

    product_no integer constraint must_be_different unique,

    name text,

    price numeric

); 

/*

-- set primary key

*/

create table products (

    product_no integer primary key,

    name text,

    price numeric

); 

create table example (

     a integer, 

     b integer, 

     c integer, 

     primary key (a, c)

); 

create table example (

     a integer, 

     b integer, 

     c integer, 

     constraint pk_example primary key (a, c)

); 

/*

-- set foreign key

*/

create table orders (

     order_id integer primary key,

     product_no integer references products (product_no)

     quantity integer

); 

create table orders (

     order_id integer primary key,

     product_no integer references products,

     quantity integer

); 

create table t1 (

   a integer primary key,

   b integer,

   c integer,

   foreign key (b, c) references other_table (c1, c2)

); 

create table order_items (

     product_no integer references products,

     order_id integer references orders,

     quantity integer,

     primary key (product_no, order_id)

); 

/*

-- set cascade

*/

-- restrict prevents deletion of a referened row.

-- cascade specifies when a referenced row is deleted, 

-- row(S) referencing it should be automatically deleted as well.

create table products (

     product_no integer primary key,

     name text,

     price numeric

);

create table orders (

     order_id integer primary key,

     shipping_address text,

     ...

);

create table order_items (

     product_no integer references products on delete restrict,

     order_id integer references orders on delete cascade,

     quantity integer,

     primary key (product_no, order_id)

);