目录

键表的约束

-- 使用check 关键字进行字段约束
create table users (
  id int primary key,
  name varchar not null,
  age int default 20 check (age > 0 and age <=150),
  created_at timestamp with time zone default now(),
  updated_at timestamp without time zone,
  sex boolean
);

create table books (
  book_no int unique not null,
  price numeric CHECK (price>0),
  book_name varchar not null,
  cost_price numeric CHECK (const_price >0),
  created_at timestamp default now(),
  updated_at timestamp with time zone default now(),
  deleted_at timestamp without time zone,
  check (cost_price < price),
)

postgres=# \d books;
                           Table "public.books"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 book_no    | integer                     |           | not null |
 price      | numeric                     |           |          |
 book_name  | character varying           |           | not null |
 cost_price | numeric                     |           |          |
 created_at | timestamp without time zone |           |          | now()
 updated_at | timestamp with time zone    |           |          | now()
 deleted_at | timestamp without time zone |           |          |
Indexes:
    "books_book_no_key" UNIQUE CONSTRAINT, btree (book_no)
Check constraints:
    "books_check" CHECK (cost_price < price)
    "books_cost_price_check" CHECK (cost_price > 0::numeric)
    "books_price_check" CHECK (price > 0::numeric)

修改表

使用 ALTER TABLE 来实现

增加字段

ALTER TABLE books ADD　COLUMN book_author varchar(200) CHECK (book_author <> '');

删除字段

ALTER TABLE books DROP COLUMN book_author;

ALTER TABLE class drop column class_no CASCADE; 删除students 表上的外键

增加约束(索引)

ALTER TABLE books CHECK(price >= 0.1);

ALTER TABLE books CONSTARINT unique_book_author_book_no UNIQUE (book_no,book_author);

ALTER TABLE books ALTER COLUMN book_author SET NOT NULL;

删除约束(索引)

ALTER TABLE books drop CONSTARAINT <约束名称>;

非空约束是没有名称的！

删除非空约束

ALTER TABLE books ALTER COLUMN book_author DROP NOT NULL;

修改默认值

ALTER TABLE books ALTER COLUMN price SET DEFAULT 0;

删除默认值

ALTER TABLE books ALTER COLUMN price drop DEFAULT;

修改字段类型

ALTER TABLE books ALTER COLUMN book_author TYPE text;

ALTER TABLE books ALTER COLUMN created_at TYPE timestamp with time zone;

ALTER TABLE books ALTER COLUMN author type varchar(32)[] USING (author::varchar32[]);

重命名字段

ALTER TABLE books RENAME COLUMN book_author TO author;

重命名表

ALTER TABLE books RENAME TO book;