SQL Basic Learning Note (iii)-Constraints

constraints:

I. When creating a table, define constraints

CREATE TABLE EMP2 (                  ID number (constraint) emp2_id_nn not null,                  name VARCHAR2 (a) not NULL,                  salary number (10 , 2)                  ) constraint Emp2_id_nn  to restrict a name to increase the reading, do not write also line, will automatically add sys_xxxx

Scope of Action:
① column-level constraints only work on one column

② table-level constraints can function on multiple columns (of course, table-level constraints can also work on a column)

Defined by: The column constraint must follow the definition of the column, and the table constraint is not associated with the column, but is defined separately.

Note that a non-null (NOT NULL) constraint can only be defined on a column

1.not null constrained column-level constraints

Insert into Emp2values (1001,null,2000)

can be null without defining a NOT NULL constraint, defined as NOT NULL cannot be null

2.unique constraint (UNIQUE constraint) column-level, table-level constraint

Insert into Emp2values (1001,null,2000)

Write a few, you will insert a few, but the ID can only have one, so the addition of data to add a constraint, can not assign the same value

CREATE TABLE Emp3 (                  ID number) constraint emp3_id_uk unique,                  name VARCHAR2 constraint emp3_name_nn NOT NULL ,                  salary Number (10,2),                  email varchar2,                  --table-level constraint                   constraint emp3_email_uk unique (email)                  )

Only unique constraints, without notnull constraints, can be assigned null values, and null values, not as a standard for duplicate content, that is, only null can be duplicated, other values can not be repeated

3.primary key (primary KEY constraint) column-level, table-level constraint
For example, in the database, the employee table to look up a person's information, the name of a lot is repeated, this is not good to check, but everyone's ID number is unique, the role of the primary key is similar to a social security number

create table Emp4 (ID number) constraint EMP4_ID_PK primary key, name VARCHAR2 (constraint emp4)  _name_nn NOT NULL, salary number (10,2), email varchar2, constraint Emp4_email_uk Unique (email)) 

Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID Number (10)
NAME VARCHAR2 (20)
SALARY number (10,2) Y
EMAIL VARCHAR2 (Y)

Primary key requirements are non-null, unique, equivalent to automatic addition of the limit of not null,unique

CREATE TABLE EMP5 (                  ID number),                  name VARCHAR2 () constraint emp5_name_nn not NULL,                  salary number (10,2), C3/>email varchar2,                  --table-level constraint                   constraint emp5_email_uk unique (email),                   constraint EMP5_ID_PK primary key (ID)                  )

4.foreign key (foreign key) column-level, table-level constraint

Like two circle of friends, a person belongs to a circle, also belongs to the B circle, this person from a circle connected to the B circle, then this person is a foreign key
I and C do not know, but through B can and C know, this B is foreign key

CREATE TABLE EMP6 (                  ID number),                  name VARCHAR2 () constraint emp6_name_nn not NULL,                  salary number (10,2), C3/>email varchar2,                  department_id number,                  --table-level constraint                   constraint emp6_email_uk unique (email),                   Constraint EMP6_ID_PK primary key (ID),                   constraint EMP6_DEPT_ID_FK foreign key (department_id) references Departments (department_id)                  )

when inserting data, department_id must be in the Departments table to insert

Attention:
On delete CASCADE (cascade delete): When a column in the parent table is deleted, the corresponding column in the child table is also deleted, that is, section 60th of the departments table is deleted, and the 60 doors in the EMP6 table
All the people also deleted

On DELETE SET null (cascade NULL): The corresponding column in the child table is empty, that is, the departments table in the 60th department blank, the EMP6 table of the original 60 department of the person's department ID is NULL, but the person is still

--cascade NULL CREATE TABLE EMP7 (                ID number),                name VARCHAR2 (constraint emp7_name_nn not NULL,                salary number ( 10,2),                email varchar2,                department_id number,--table-level constraint constraint Emp7_email_uk unique (email), Constraint EMP7_ID_PK primary key (ID), Constraint EMP7_DEPT_ID_FK foreign key (DEPARTMENT_ID) references departments ( department_id) on delete set null                   )

5.check constrained column-level, table-level constraints

Check Constraint for Salary

CREATE TABLE EMP8 (                ID number),                name VARCHAR2 (a) constraint emp8_name_nn not NULL, salary number                (10,2) Constraint emp8_salary Check (Salary > Salary < 20000),                email varchar2,                department_id number ( ,--table-level constraint constraint Emp8_email_uk unique (email), Constraint EMP8_ID_PK primary key (ID), constraint EMP8_DEPT_ID_FK Foreign KEY (DEPARTMENT_ID) references departments (department_id) on delete set null                   )

Limit Salary in (1500,20000)


Second, after creating the table, add the constraint
1. Use the ALTER TABLE statement:
Add or remove constraints, but cannot modify constraints
2. Effective or invalid constraint
3. Add a not NULL constraint to use the MODIFY statement

1. Add a NOT NULL constraint

ALTER TABLE emp5modify (Salary number (11,2) NOT NULL)

2. Delete

3. Add a UNIQUE constraint

ALTER TABLE EMP5ADD constraint Emp5_name_uk unique (name)

4. Invalidating a constraint

The Emp3 table has a unique constraint that invalidates it, but does not delete

sql> ALTER TABLE Emp3  2  disable constraint emp3_email_uk;

5. Effective, if the data of the column should conform to the constraints of the constrained constraint, such as the unique constraint, in order to be effective, it is necessary to ensure that the data of this column cannot be duplicated.

sql> ALTER TABLE Emp3  2  enable constraint Emp3_email_uk;

6. Query constraints

SELECT constraint_name, constraint_type,search_conditionfrom user_constraintswhere table_name = ' EMPLOYEES ';

7. Querying for columns that define constraints

SELECT constraint_name, Column_namefromuser_cons_columnswhere table_name = ' EMPLOYEES ';

Constraints need to be aware of the place

1). * * Non-NULL constraints (NOT NULL) can only be defined at the column level
2). * * The column value of UNIQUE constraint can be null
3 ). * * The column referenced by the foreign key (foreign key) must have at least one unique constraint

SQL Basic Learning Note (iii)-Constraints