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:
- 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 |
mailbox | varchar2(30) | no | - | only | |
regtime | Date of registration | date | no | sysdate | - |
- 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);