The role of constraints: defining rules, ensuring integrity
One, non-null constraints
1. Setting a non-null constraint when creating a table
CREATE TABLE Userinfo_1
(ID number (6,0),
Username VARCHAR2 () not NULL,
Userpwd varchar2 () NOT NULL
)
2. Setting a non-null constraint when modifying a table
DELETE from UserInfo
ALTER TABLE UserInfo
MODIFY username varchar2 () not NULL
3. Go unless the null constraint
ALTER TABLE UserInfo
MODIFY username VARCHAR2 () null
Second, PRIMARY KEY constraint: Ensure the uniqueness of each row of data in a table (a table can have only one PRIMARY KEY constraint)
1. Setting a PRIMARY KEY constraint when creating a table
CREATE TABLE Userinfo_2
(ID number (6,0) PRIMARY KEY,
Username VARCHAR2 (20),
Userpwd VARCHAR2 (20)
)
CREATE TABLE userinfo_2s
(
ID Number (6,0),
Username VARCHAR2 (20),
Userpwd varchar2 (20),
CONSTRAINT pk_id_username PRIMARY KEY (id,username))
2. Adding a primary key when modifying a table
ALTER TABLE UserInfo
ADD CONSTRAINT pk_id PRIMARY KEY (ID)
Change the name of a constraint
ALTER TABLE UserInfo
RENAME CONSTRAINT pk_id to new_pk_id
3. Delete a PRIMARY KEY constraint
disabling constraints
ALTER TABLE UserInfo
DISABLE constraint new_pk_id
Delete constraint (drop PRIMARY KEY)
ALTER TABLE UserInfo
DROP CONSTRAINT new_pk_id
ALTER TABLE Userinfo_2
DROP PRIMARY KEY
Third, FOREIGN KEY constraints
1. Setting a FOREIGN KEY constraint when creating a table
Create a primary table
CREATE TABLE TypeInfo
(
Type VARCHAR2 (Ten) primary key,
TypeName VARCHAR2 (20)
)
Create from table and add foreign KEY constraint (column level)
CREATE TABLE Userinfo_f
(
ID VARCHAR2 (Ten) primary key,
Username VARCHAR2 (20),
Typeid_new VARCHAR2 (Ten) References TypeInfo (type)
)
Enter a value into the primary table
INSERT into TypeInfo values (+)
Enter a value from the table (the value entered for the FOREIGN key constraint is either a value in the primary table, or a null value)
Insert into Userinfo_f (id,typeid_new) VALUES (+)
Insert into Userinfo_f (id,typeid_new) VALUES (1,null)
Table-Level
CREATE TABLE Userinfo_f2
(
ID VARCHAR2 (Ten) primary key,
Username VARCHAR2 (20),
Typeid_new VARCHAR2 (10),
CONSTRAINT fk_typeid_new foreign KEY (typeid_new) references TypeInfo (type) on DELETE cascade and setting cascade
)
2. Add a foreign KEY constraint when modifying a table
CREATE TABLE Userinfo_f4
(ID varchar2 (primary key),
Username VARCHAR2 (20),
Typeid_new VARCHAR2 (10)
)
ALTER TABLE Userinfo_f4
ADD CONSTRAINT fk_typeid_alter foreign Key (typeid_new) references TypeInfo (type)
3. Delete a FOREIGN KEY constraint
Disable
ALTER TABLE Userinfo_f4
DISABLE CONSTRAINT Fk_typeid_alter
Delete
ALTER TABLE Userinfo_f4
DROP CONSTRAINT Fk_typeid_alter
Iv. UNIQUE constraints: ensuring uniqueness of field values
The primary key field that is constrained by the primary key must be non-null (only one of the tables) unique constraints allow an empty set (a table can have more than one)
1. Set a unique constraint when creating a table
Column Level Add unique
CREATE TABLE Userinfo_u
(
ID VARCHAR2 (10),
Username VARCHAR2 (a) unique,
Userpwd VARCHAR2 (20)
)
Table-level set UNIQUE constraint constraint name unique
CREATE TABLE USERINFO_U1
(
ID VARCHAR2 (primary key),
Username VARCHAR2 (20),
CONSTRAINT un_username Unique (username)
)
2. Adding a unique constraint when modifying a table
CREATE TABLE USERINFO_U2
(
ID VARCHAR2 (primary key),
Username VARCHAR2 (20)
)
ALTER TABLE USERINFO_U2
ADD CONSTRAINT un_username_new Unique (username)
3. Delete a constraint
disabling UNIQUE constraints
ALTER TABLE USERINFO_U2
DISABLE CONSTRAINT un_username_new
Delete
ALTER TABLE USERINFO_U2
DROP CONSTRAINT un_username_new
Five, check the constraints
1. Set CHECK constraints when creating a table
Column level check (constraint)
CREATE TABLE Userinfo_c
(
ID VARCHAR2 (Ten) primary key,
Username VARCHAR2 (20),
Salary Number (5,0) check (salary>0)
)
Setting check constraints at the table level
CREATE TABLE Userinfo_d
(
ID VARCHAR2 (Ten) primary key,
Username VARCHAR2 (20),
Salary number (5,0),
CONSTRAINT ck_salary Check (salary>0)
)
2. Add a check constraint when modifying a table
CREATE TABLE Userinfo_d1
(
ID VARCHAR2 (Ten) primary key,
Username VARCHAR2 (20),
Salary Number (5,0)
)
ALTER TABLE Userinfo_d1
ADD CONSTRAINT ck_salary_new Check (salary>0)
3. Delete CHECK constraints
Disable Disable
ALTER TABLE USERINFO_D1
Disable constraint ck_salary_new
Delete Drop
ALTER TABLE USERINFO_D1
Drop constraint Ck_salary_new
Constraints of Oracle Database development SQL Foundation