FOREIGN KEY constraints and the correspondence between entities in the database (1==1,1==n,n==n)

1.1.1 FOREIGN KEY constraint

Create database day16;

Use DAY16;

To create a departmental table:

CREATE TABLE Dept (

did int primary key auto_increment,

Dname varchar (20)

);

INSERT INTO dept values (NULL, ' development department ');

INSERT INTO dept values (NULL, ' Research Department ');

INSERT INTO dept values (NULL, ' marketing department ');

Create an employee table:

CREATE TABLE EMP (

Eid int primary Key auto_increment,

ename varchar (20),

Email varchar (20),

Eprice Double

);

INSERT into EMP values (null, ' Zhang San ', ' zs.itcast.cn ', 4500);

INSERT into EMP values (NULL, ' John Doe ', ' ls.itcast.cn ', 5500);

INSERT into EMP values (null, ' Harry ', ' ww.itcast.cn ', 6500);

INSERT into EMP values (NULL, ' Zhao Liu ', ' zl.itcast.cn ', 7500);

Now describe the relationship between department and employee:

* You need to add a field to the employee table. The field is the department number!!!

ALTER TABLE EMP add DNO int;

* Modify employee to add the corresponding department:

Update emp Set DNO = 1 where Eid = 1;

Update emp Set DNO = 2 where Eid = 2;

Update emp Set DNO = 3 where Eid = 3;

Update emp Set DNO = 3 where Eid = 4;

* now the market is depressed, Delete Marketing department, deleted the marketing department, so how to deal with the employees of the marketing department?

* If you delete a department now, then the employee is still there and the department that is pointing to it is the one that was just deleted!!!

* must use FOREIGN KEY constraints:

* single table constraint the role of: to ensure the integrity of the data.

* FOREIGN KEY constraints: guarantees data integrity between multiple tables.

* FOREIGN key: FOREIGN KEY Constraints

* ALTER TABLE EMP add foreign KEY (DNO) References dept (did);

1.1.2 Multi-table design three entity relationships: one-to-many:

A department can correspond to multiple employees, an employee can only belong to one department!

A customer can correspond to multiple orders, an order can only belong to one customer!

* A one -to-many relationship table-building principle:

* you need to add a field on more than one side, as the foreign key points to one of the primary keys!

Many-to-many:

A student can choose multiple courses and a course can be chosen by multiple students!

* the principle of many-to-many relationship building table:

* you need to create a third table, The third table requires two fields, these two fields point to the primary key for many-to-many sides, respectively, as foreign keys!

One-to-one: (less use)

A company can only correspond to one address, and one address can only correspond to one company!

One-to-one relationship is less used!

* Company table: The address is a field of the company's table.

* One-to-one relationship with the establishment of the table principle:

* PRIMARY KEY correspondence:

* Unique FOREIGN KEY correspondence:

FOREIGN KEY constraints and the correspondence between entities in the database (1==1,1==n,n==n)