Constraints of Oracle Database development SQL Foundation

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