SQL Notes-sixth chapter, Indexes and Constraints

I. Index

CREATE INDEX index name on table name (Field 1, Field 2,...... Field N)

CREATE INDEX idx_person_nameage on t_person (fname,fage)

DROP INDEX index name on table name

DROP INDEX idx_person_nameage on t_person;

two. Constraints

The following constraints are mainly provided in the database system:

Non-null constraint; unique constraint; CHECK constraint; primary key constraint; FOREIGN KEY constraint

1. Non-null constraints

CREATE TABLE T_person

(Fnumber VARCHAR () not NULL ,

FName VARCHAR (20),

Fage INT)

2. Unique constraints

CREATE TABLE T_person (fnumber varchar () UNIQUE, FName varchar (), Fage INT)

Compound UNIQUE Constraint

CONSTRAINT Constraint name unique (field 1, Field 2 ...). Field N)

CREATE TABLE T_person (fnumber VARCHAR (20),
Fdepartmentnumber VARCHAR (20),
FName VARCHAR (+), Fage INT,
CONSTRAINT unic_1 UNIQUE (Fnumber,fdepartmentnumber),
CONSTRAINT unic_2 UNIQUE (Fdepartmentnumber, FName))

ALTER TABLE T_person DROP CONSTRAINT unic_1;
ALTER TABLE T_person DROP CONSTRAINT unic_2;
ALTER TABLE T_person DROP CONSTRAINT unic_3;

3.CHECK constraints

CREATE TABLE T_person (
Fnumber VARCHAR (20),
FName VARCHAR (20),
Fage INT CHECK(Fage >0),
Fworkyear INT CHECK(fworkyear>0))

ALTER TABLE T_person
ADD CONSTRAINT ck_2 CHECK (fage>14)

4. PRIMARY KEY constraints

Add the PRIMARY key keyword after the field definition to

Additional

ALTER TABLE T_person
ADD CONSTRAINT pk_1 PRIMARY KEY (fnumber,fname)

FOREIGN KEY constraints

CREATE TABLE T_author
(
FId VARCHAR (PRIMARY KEY),
FName VARCHAR (+),
Fage INT,
FEMAIL VARCHAR (a)
);
CREATE TABLE T_book
(
FId VARCHAR (PRIMARY KEY),
FName VARCHAR (+),
Fpagecount INT,
Fauthorid VARCHAR (+),
FOREIGN KEY (fauthorid) REFERENCES t_author (FId)
);

Delete

DELETE From T_book WHERE fauthorid =5;

DELETE From T_author WHERE fname= ' Badboy '

Additional

ALTER TABLE T_book
ADD CONSTRAINT Fk_book_author
FOREIGN KEY (Fauthorid) REFERENCES t_author (FId)

SQL Notes-sixth chapter, Indexes and Constraints