Oracle--ddl

First, some concepts

Defined:

Primary Key-uniquely identifies a record, cannot be duplicated, is not allowed to be empty

Foreign key--the foreign key of the table is the primary key of another table, the foreign key can have duplicate, can be a null value

Index--The field does not have duplicate values, but can have a null value

Role:

Primary key--to ensure data integrity

Foreign keys--used to connect to other tables (checks are made when data is added, values not in the associated table are added)

Index--is to improve the speed of query sorting

Number:

Primary key--the primary key can only have one

Foreign key--a table can have multiple foreign keys (such as: Student table, curriculum, student curriculum, where the student curriculum is two primary keys, respectively, the student table and curriculum of the foreign key, is the table's combined primary key)

Index--A table can have multiple unique indexes

Build a Table statement

The role of foreign key settings:

Foreign key value rule: A null value or a reference primary key value.

(1) When a non-null value is inserted, it cannot be inserted if the value is not in the primary key table.

(2) When updating, you cannot change the value that is not in the primary key table.

(3) When you delete a primary key table record, you can either cascade Delete or reject it when you select a foreign key record while the foreign key is being constructed.

(4) When updating a primary key record, there is also a selection of cascading updates and rejected executions.

In short, the primary key and foreign key of SQL is the binding function.

Second, create the statement

To create a table:

Create Tableemp_dept (empno Number(4)Primary Key  not NULL,--The first way to create a primary keyEnamevarchar2(Ten), Deptno Number(2), Deptnamevarchar2( -)); Comment on columnEmp_dept.empno is 'User Number'; Comment on columnEmp_dept.ename is 'User name';

To create a primary key:

Method One: When creating a table, declare it when the primary key field is defined.

Method Two: Declare the primary key at the bottom of the creation table

Create Tableemp_dept (empno Number(4) not NULL,--The first way to create a primary keyEnamevarchar2(Ten), Deptno Number(2), Deptnamevarchar2( -),       constraintPk_emp_deptPrimary Key("Empno"));

Method Three: Use the alert statement

Alter Table Table name Add constraint Pk_ Field name --"PK" is the abbreviation for the primary key, the field name is the name of the field on which to create the key, ' pk_ field name ' is the constraint name primarykey-- field name Ibid .

To create a foreign key:

Way One:

Create Table SC (    char(5constraint fk_student references student (SNO),--  Write in attribute definition    char(5),    foreignkey  References  Course (CNO),    number);

Way two:

Create Table SC (    char(5),    Char(5),     foreignkeyreferences  course (CNO),    grade number,    
Constraint fk_student foreign KEY (SNO) references student (SNO)
);

Way three:

Alter Table SC Add constraint fk_student  foreignkey(SNO)  references student (SNO);

To delete a foreign key:

Alter Table Drop constraint fk_student;

Iii. examples

Create student, course, Stu_cou table, one is student table, one is curriculum, one is Student Curriculum association table:

Student

Create Table Student (       number (6primarykeynotnull,        varchar2 ());

Course

Create Table Course (       number (6notnull,       varchar2(  - ),       constraintprimarykey  (CNO));

Stu_cou

Create TableStu_cou (Sno Number(6) not NULL, CNO Number(6) not NULL constraintFk_courseReferencesCourse (CNO) on DELETE cascade,--cascading query create_time DatedefaultSysdate,Foreign Key(SNO)ReferencesStudent (SNO) on DELETE Cascade-cascade query );

Insert one piece of data per table

Insert  intoStudent (Sno,sname)Values(1,'Bright');Insert  intoCourse (cno,cname)Values(1,'Java');Insert  intoStu_cou (SNO,CNO)Values(1,1);

This data is legal, first the primary key satisfies the unique constraint, the foreign key satisfies the constraint, at this time, inserts an illegal data

Insert  into  Values(1,2);     

Delete a piece of data

Delete  from course;

You'll see that the data in the Stu_cou table is deleted.

Oracle--ddl