5 Constraints available in Oracle database

Constraint: Used to maintain data integrity and prevent invalid data from entering the database.
The 5 constraints provided in the Oracle database are defined as a combination of a column or a column.
1. PRIMARY KEY constraint (PRIMARY key): A single row can be uniquely identified in a table. The primary key can be qualified on more than one column.
2. Unique key constraint (unique key): A single row can be uniquely identified in a table, and a unique key can be qualified on more than one column.
the difference between a primary key and a unique key:
A , there can be at most one primary key in a table. You can have multiple unique keys.
B, the column qualified by the primary key cannot be null, and the column qualified by the unique key can be null.
3. FOREIGN KEY constraint (FOREIGN key):
Reference table: The table that contains the FOREIGN KEY constraint for the column.
Referenced table: The column referenced by the foreign key. The referenced column can only be a primary key or a unique key.
4, Non-empty constraint (NOT NULL): Indicates that the data on the column cannot have a null value.
5. Check: Indicates that the data on the column must satisfy a certain condition expression.

How to create a constraint:
1. Create a constraint while creating a table
A. Column-level constraints: Write constraints after each column is written.
CREATE TABLE t_08132 (ID number (PRIMARY) KEY,
NAME CHAR (Ten) is not NULL,
Gender CHAR (2) CHECK (Gender in (' Male ', ' female ')),
Email CHAR (UNIQUE)
)
INSERT into t_08132 VALUES (1, ' a ', ' Male ', ' [email protected] ')
INSERT into t_08132 VALUES (2, ' a ', ' Male ', ' [email protected] ')
INSERT into t_08132 VALUES (3, ' C ', ' Male ', ' [email protected] ')
INSERT into t_08132 VALUES (4, ' d ', ', ' [email protected] ')
INSERT into t_08132 VALUES (5, ' e ', ' Demon ', ' [email protected] ')
FOREIGN KEY constraints
CREATE TABLE t_08133 (ID number (PRIMARY) KEY,
NAME CHAR (Ten) is not NULL,
Deptno Number (2) REFERENCES Dept (DEPTNO)
)
INSERT into t_08133 VALUES (1, ' a ', 20)
To name a constraint
CREATE TABLE t_08134 (ID number) CONSTRAINT t4_id_pk PRIMARY KEY,
NAME CHAR (Ten) CONSTRAINT T4_name_nn not NULL,
Gender CHAR (2) CONSTRAINT T4_gen_ck CHECK (Gender in (' Male ', ' female ')),
Email CHAR (CONSTRAINT) T4_em_uk UNIQUE,
Deptno Number (2) CONSTRAINT T4_DEPTNO_FK REFERENCES Dept (DEPTNO)
)
INSERT into t_08134 VALUES (5, ' e ', ' Male ', ' [email protected] ', NULL)
INSERT into t_08134 VALUES (6, ' e ', ' Male ', ' [email protected] ', NULL)
B. Table-level constraints: Write constraints after all columns have been written. Note that the Notnull constraint cannot be written at the table level.
CREATE TABLE t_08135 (ID number (10),
NAME CHAR (Ten) CONSTRAINT T5_name_nn not NULL,
Gender CHAR (2),
Email CHAR (20),
Deptno Number (2),
CONSTRAINT T5_ID_PK PRIMARY KEY (ID),
CONSTRAINT T5_gen_ck CHECK (Gender in (' Male ', ' female ')),
CONSTRAINT t5_em_uk UNIQUE (email),
CONSTRAINT t5_deptno_fk FOREIGN KEY (deptno) REFERENCES Dept (DEPTNO)
)
Name of constraint: The constraint type of the table in which the general rule constraint resides
EMP_EMPNO_PK, usually named only for primary key, foreign key, unique key.

2. Create a constraint after creating a table
Grammar:
Alter table name ADD [CONSTRAINT constraint name] constraint type [(List of column names)]
CREATE TABLE t08161 (ID number,name char (), Sex char (2), email CHAR (ten), Deptno number)
Add a PRIMARY KEY constraint on the ID field
ALTER TABLE t08161 ADD CONSTRAINT t1_id_pk PRIMARY KEY (ID)
Add an inspection constraint on the sex field
ALTER TABLE t08161 ADD CONSTRAINT t1_email_ck CHECK (Sex in (' Male ', ' female '))
Add a Uniqueness constraint to your email
ALTER TABLE t08161 ADD CONSTRAINT t1_email_uk UNIQUE (email)
Add a FOREIGN KEY constraint on DEPTNO, referring to the Deptno field of Dept.
ALTER TABLE t08161 ADD CONSTRAINT t1_deptno_fk FOREIGN KEY (deptno) REFERENCES Dept (DEPTNO)
Add a non-null constraint on name
ALTER TABLE t08161 ADD CONSTRAINT t1_name_nn not NULL (name)I can't add it.

ALTER TABLE t08161 MODIFY NAME NULL
ALTER table name MODIFY field name Null/not null //Set one of the fields by the syntax null or NOT NULL.
Practice:
Create a table student table containing the school number (primary key), name (non-empty), age, entry date,
Class number (foreign key, referenced in Dept Table Department number)
Requires that a constraint be established after the table is created.
CREATE TABLE t08162 (Sno number, sname CHAR (Ten), sage number, indate date,classno number)
ALTER TABLE t08162 ADD PRIMARY KEY (SNO)
ALTER TABLE t08162 MODIFY SNAME not NULL
ALTER TABLE t08162 ADD FOREIGN KEY (classno) REFERENCES Dept (DEPTNO)
4. How to delete a constraint
ALTER table name DROP PRIMARY key| UNIQUE (column) | CONSTRAINT Constraint name
ALTER TABLE t08162 DROP PRIMARY KEY
Exercise: Delete A foreign key constraint on a classno on a t08162
ALTER TABLE t08162 DROP CONSTRAINT sys_c005467
5, the restriction is enabled and disabled.
Grammar:
Alter Table name disable| ENABLE CONSTRAINT constraint name

turn from: http ://www.blogjava.net/stevenjohn/archive/2012/09/04/387023.html

5 constraints provided in the Oracle database