Oracle Learning article Seven: constraints

----constraint-------

--1. PRIMARY KEY constraints
--Unique identifier, cannot be empty, usually used for ID
--1> Creating a primary key
CREATE TABLE Person (
ID VARCHAR2 (primary key),
Name VARCHAR2 (50),
Birthday date,
Sex VARCHAR2 (2) Default ' male '
);
Insert into person (id,name,birthday,sex)
VALUES (1, ' Zhangsan ', to_date (' 1988-05-11 ', ' yyyy-mm-dd '), ' Male ');
select * from person;
Insert into person (id,name,birthday,sex)
VALUES (1, ' Zhangsan ', to_date (' 1988-05-11 ', ' yyyy-mm-dd '), ' Male ');

--2> Create a primary key and name
CREATE TABLE person{
ID VARCHAR2 (20),
Name VARCHAR2 (50),
Birthday date,
Sex VARCHAR2 (2) Default ' male ',
Constraint PERSON_ID_PK primary key (ID)
);

--2. Unique constraints
--unique only one PRIMARY KEY constraint can be built in a table, and a unique constraint is available if the other columns do not want duplicates.

CREATE TABLE Person (
ID VARCHAR2 (primary key),
Name VARCHAR2 () unique NOT NULL,
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male '
);

--3. Non-null constraints
--not null cannot be empty.
CREATE TABLE Person (
ID VARCHAR2 (primary key),
Name VARCHAR2 (a) NOT null,--non-null constraint
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male '
);

--4. Checking constraints
--check must meet inspection conditions
CREATE TABLE Person (
ID VARCHAR2 (primary key),
Name VARCHAR2 () NOT NULL,
Age Number (3) check (age between 0 and 100),
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male ' Check (sex in (' Male ', ' female '))
);

eg

CREATE TABLE Person (
ID VARCHAR2 (20),
Name VARCHAR2 () NOT NULL,
Age Number (3),
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male ',
Constraint PERSON_ID_PK primary key (ID),
Constraint Person_name_uq Unique (name),
Constraint person_age_ck Check (age between 0 and 100)
);

--5. Primary-FOREIGN KEY constraint (two tables for constrained operation.) )
drop table person;
drop table book;
CREATE TABLE Person (
PID VARCHAR2 (20),
Name VARCHAR2 () NOT NULL,
Age Number (3),
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male ',
Constraint PERSON_ID_PK primary key (ID),
Constraint Person_name_uk Unique (name),
Constraint person_age_ck Check (age between 0 and 100)
);

CREATE TABLE book (
Bid number primary Key,
Bname varchar2 () Unique NOT NULL,
Bprice number (5,2),
PID varchar (20),
Constraint PERSON_BOOK_PID_FK foreign KEY (PID) references person (PID)
);

-- Note:
1. The foreign key set in the child table must be the primary key in the parent table.
2. When deleting a record, you must delete the Word table record before deleting the parent table.
drop table book;
drop table person;
Forced Delete: drop table book cascade constraint; (forced to delete with parent table)

Cascade Delete: If you want the association to delete the child table cascade when you delete the primary table

drop table person;
drop table book;
CREATE TABLE Person (
PID VARCHAR2 (20),
Name VARCHAR2 () NOT NULL,
Age Number (3),
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male ',
Constraint PERSON_ID_PK primary KEY (PID),
Constraint Person_name_uk Unique (name),
Constraint person_age_ck Check (age between 0 and 100)
);
CREATE TABLE book (
Bid number primary Key,
Bname varchar2 () Unique NOT NULL,
Bprice number (5,2),
PID varchar (20),
Constraint PERSON_BOOK_PID_FK foreign KEY (PID) references person (PID) on DELETE cascade--on DELETE Cascade
);
Insert into person (pid,name,age,birthday,sex)
VALUES (1, ' Zhangsan ', 20,to_date (' 1988-10-11 ', ' yyyy-mm-dd '), ' Male ');
Insert into book (BID,BNAME,BPRICE,PID)
VALUES (1001, ' J2se ', 30, 1);

select * from person;
SELECT * from book;

Delete from the person where pid=1;--the corresponding sub-table book is also deleted with PID 1.

6. Modifying constraints
drop table person;
CREATE TABLE Person (
PID VARCHAR2 (20),
Name VARCHAR2 () NOT NULL,
Age Number (3),
Birthday date NOT NULL,
Sex VARCHAR2 (2) Default ' male '
);

ALTER TABLE person add constraint PERSON_PID_PK primary key (PID);
ALTER TABLE person add constraint Person_name_uk unique (name);
ALTER TABLE person add constraint person_age_ck check (age between 0 and 150);
ALTER TABLE person add constraint person_age_ck check (sex in (' Male ', ' female '));

--Increase the primary foreign KEY constraint
drop table book;
CREATE TABLE book (
Bid number,
Bname varchar2 (20),
Bprice Namber (5,2),
PID varchar (20)
);
ALTER TABLE book add constraint BOOK_BID_PK primary key (BID);
ALTER TABLE book add constraint PERSON_BOOK_PID_FK foreign key (PID) references person (PID)

7.rownum represents the row number, and the pseudo-column exists in each table.

Select Rownum,ename from emp where rownum <=2;

Select Rownum,ename from emp where rownum between 2 and 5;-- cannot be directly used for intermediate queries with rownum

--correct wording, usually for paging queries
SELECT * FROM (select RowNum rn,ename from emp where rownum <5) temp where temp.rn>2;

8. Collection Operations
CREATE TABLE EMP20 as SELECT * from EMP where rownum<5;
1 Union and (returns the sum of the results, no repetition)
SELECT * FROM emp
Union
SELECT * FROM EMP20--Returns all 14 records in EMP

2 "union All and (returns the sum of the results, including duplicates)
SELECT * FROM emp
UNION ALL
SELECT * FROM EMP20--returns 18 records

3 "intersect (return the same part of the result)
SELECT * FROM emp
Intersect
SELECT * FROM EMP20--Returns 4 records

4 "Minus difference (return result difference set)
SELECT * FROM emp
Minus
SELECT * FROM EMP20--returns 10 records

Oracle Learning article Seven: constraints