1, Design table

Premise: the design table should first follow the three paradigms as needed

Review 3NF:

• 1NF – > column is the smallest atom that cannot be subdivided (avoid duplication);
• 2NF – > primary key dependency (unique);
• 3NF – > eliminate the transfer dependency (establish the primary foreign key Association split table);

1.1. Determine table name

Table name tb_user
Table name tb_txt

1.2. Determine the field name type + constraint (default check for non empty primary key and foreign key)

(I)
Primary key: uniquely identifies a record (unique and non empty)
Unique: unique
Non empty: cannot be empty
Default: use a given default value when no value is given
Foreign key: refer to a (some) field of other tables (yourself)
Checking: custom rules

example:

1. User table

Table name tb_user
Primary key userid

Field name	chinese	type	Empty	Default value	Other instructions
userid	Serial number	number(5)	no	-	Primary key
username	user name	varchar2(30)	no	-	Length 4-20
userpwd	password	varchar2(20)	no	-	Length 4-18
age	Age	number(3)	no	18	Greater than > = 18
gender	Gender	char(2)	no	male	Male or female
email	mailbox	varchar2(30)	no	-	only
regtime	Date of registration	date	no	sysdate	-

1. Article table

Table name tb_txt
Primary key txtid

Field name	chinese	type	Empty	Default value	Other instructions
txtid	Serial number	number(10)	no	-	Primary key
title	title	varchar2(32)	no	-	Length 4-30
txt	text	varchar2(1024)	No-
pubtime	Release time	date	no	sysdate	-
userid	Publisher	number(5)	no	-	Foreign key, refer to tb_user's userid column

2, Create table

The table name must be unique. If it exists, it must be deleted

create table Table name(
Field name type(length) constraint,
...Other fields....
..constraint........
);

2.1. Create table (unconstrained)

tb_user table:

create table tb_user(
userid number(5),
username varchar2(30),
userpwd varchar2(20),
age number(3) ,
gender char(2) ,
email varchar2(30),
regtime date
);
--Add note
comment on table tb_user is 'User table';
comment on column tb_user.userid is 'Serial number,Primary key';
comment on column tb_user.username is 'user name';
comment on column tb_user.userpwd is 'password';
comment on column tb_user.age is 'Age';
comment on column tb_user.gender is 'Gender';
comment on column tb_user.email is 'mailbox';
comment on column tb_user.regtime is 'Date of registration';

tb_txt table:

create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);
--notes
comment on table tb_txt is 'Article table';
comment on column tb_txt.txtid is 'Serial number,Primary key';
comment on column tb_txt.title is 'title';
comment on column tb_txt.txt is 'text';
comment on column tb_txt.pubtime is 'Release time';
comment on column tb_txt.userid is 'Publisher,Foreign keys, reference tb_user of userid column';

2.2. Create table (create constraint + default name at the same time)

This method of creating constraints while creating tables and using the default constraint name is not recommended because it is inconvenient to troubleshoot later. Its main advantage is simplicity.

--Delete (delete the slave table first and then the master table; delete the constraint at the same time)
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

tb_user table:

create table tb_user(
userid number(5) primary key,
username varchar2(30) check(length(username) between 4 and 20) not null ,
userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18),
gender char(2) default('male') check(gender in('male','female')),
email varchar2(30) unique,
regtime date default(sysdate)
);
--Add note
comment on table tb_user is 'User table';
comment on column tb_user.userid is 'Serial number,main
 key';
comment on column tb_user.username is 'user name';
comment on column tb_user.userpwd is 'password';
comment on column tb_user.age is 'Age';
comment on column tb_user.gender is 'Gender';
comment on column tb_user.email is 'mailbox';
comment on column tb_user.regtime is 'Date of registration';

tb_txt table:

create table tb_txt(
txtid number(10) primary key,
title varchar2(32) not null check(length(title)>=4 and length(title)<=30),
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) references tb_user(userid) on delete set null
);
--notes
comment on table tb_txt is 'Article table';
comment on column tb_txt.txtid is 'Serial number,Primary key';
comment on column tb_txt.title is 'title';
comment on column tb_txt.txt is 'text';
comment on column tb_txt.pubtime is 'Release time';
comment on column tb_txt.userid is 'Publisher,Foreign keys, reference tb_user of userid column';

2.3. Create table (create constraint + specify name at the same time)

Create a constraint and specify the name of the constraint while creating the table, which is convenient for later scheduling
Wrong, recommended

--Delete (delete the slave table first and then the master table; delete the constraint at the same time)
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

tb_user table:

create table tb_user(
userid number(5),
username varchar2(30) constraint nn_user_name not null ,
userpwd varchar2(20) constraint nn_user_pwd not null ,
age number(3) default(18) ,
gender char(2) default('male'),
email varchar2(30),
regtime date default(sysdate),
constraint pk_user_id primary key (userid),
constraint ck_user_name check(length(username)between 4 and 20) ,
constraint ck_user_pwd check(length(userpwd) between 4 and 18),
constraint ck_user_age check(age>=18),
constraint ck_user_gender check(gender in('male','female')),
constraint uq_user_email unique(email)
);
--Add note
comment on table tb_user is 'User table';
comment on column tb_user.userid is 'Serial number,Primary key';
comment on column tb_user.username is 'user name';
comment on column tb_user.userpwd is 'password';
comment on column tb_user.age is 'Age';
comment on column tb_user.gender is 'Gender';
comment on column tb_user.email is 'mailbox';
comment on column tb_user.regtime is 'Date of registration';

tb_txt table:

create table tb_txt(
txtid number(10),
title varchar2(32) constraint nn_txt_title not null,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) ,
constraint pk_txt_id primary key(txtid),
constraint ck_txt_id check(length(title)>=4 and length(title)<=30),
constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) ondelete cascade
);
--notes
comment on table tb_txt is 'Article table';
comment on column tb_txt.txtid is 'Serial number,Primary key';
comment on column tb_txt.title is 'title';
comment on column tb_txt.txt is 'text';
comment on column tb_txt.pubtime is 'Release time';
comment on column tb_txt.userid is 'Publisher,Foreign keys, reference tb_user of userid column';

2.4. Create table (add create constraint + specify name)

Recommended for later troubleshooting

--Delete (delete the slave table first and then the master table; delete the constraint at the same time)
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

tb_user table:

create table tb_user(
userid number(5),
username varchar2(30) ,
userpwd varchar2(20) ,
age number(3) ,
gender char(2) ,
email varchar2(30),
regtime date default(sysdate)
);
--Additional constraints
alter table tb_user add constraint pk_user_id primary key (userid);
alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ;
alter table tb_user add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
alter table tb_user add constraint ck_user_age check(age>=18);
alter table tb_user add constraint ck_user_gender check(gender in('male','female'));
alter table tb_user add constraint uq_user_email unique(email);
--Non empty and default
alter table tb_user modify (username constraint nn_user_name not null);
alter table tb_user modify (userpwd constraint nn_user_pwd not null);
alter table tb_user modify (age default(18));
alter table tb_user modify (gender default('male'));
--Add note
comment on table tb_user is 'User table';
comment on column tb_user.userid is 'Serial number,main
 key';
comment on column tb_user.username is 'user name';
comment on column tb_user.userpwd is 'password';
comment on column tb_user.age is 'Age';
comment on column tb_user.gender is 'Gender';
comment on column tb_user.email is 'mailbox';
comment on column tb_user.regtime is 'Date of registration';

tb_txt table:

create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);
--Additional constraints
alter table tb_txt add constraint pk_txt_id primary key(txtid);
alter table tb_txt add constraint ck_txt_id
check(length(title)>=4 and length(title)<=30);
--Three cascading deletion rules
alter table tb_txt 
add constraint fk_txt_ref_user_id foreign key(userid)
 references tb_user(userid);

alter table tb_txt add constraint fk_txt_ref_user_id
 foreign key(userid) references tb_user(userid) on delete cascade ;

alter table tb_txt 
add constraint fk_txt_ref_user_id 
foreign key(userid) 
references tb_user(userid) on delete set null;
--Note: non null default
alter table tb_txt modify (title constraint
nn_txt_title not null) ;
alter table tb_txt modify (pubtime default(sysdate));
--notes
comment on table tb_txt is 'Article table';
comment on column tb_txt.txtid is 'Serial number,Primary key';
comment on column tb_txt.title is 'title';
comment on column tb_txt.txt is 'text';
comment on column tb_txt.pubtime is 'Release time';
comment on column tb_txt.userid is 'Publisher,Foreign keys, reference tb_user of userid column';

2.5. Copy structure in existing table

create table Table name as select Field list from Existing table where 1!=1;
--Copy structure emp
create table emp_his as select ename,sal from emp where 1!=1;
--Copy structure emp +data
create table emp_his2 as select ename,sal from emp where sal>2000;

3, Constraints (understanding)

In oracle, everything is an object, and constraints are also objects one by one. In addition to creating constraints, we can also perform some other operations on constraints

3.1. View constraints of a user

select constraint_name, constraint_type
  from user_constraints
 where owner = upper('SCOTT');

3.2. Viewing table constraints

select constraint_name, constraint_type
  from user_constraints
 where table_name = upper('emp');

3.3. View field name + constraint

select constraint_name, column_name
  from user_cons_columns
 where table_name = upper('emp');

3.4. Disabling and enabling constraints

ALTER TABLE tb_user disable constraint nn_user_name;
ALTER TABLE tb_user enable constraint nn_user_name;

3.5. Delete constraint

alter table tb_user drop constraint uq_user_email cascade;

3.6. Modify constraints

--Non empty
alter table tb_user modify (username varchar2(20));
--default
alter table tb_user modify (age default null);