Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of referential integrity in sql", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of referential integrity in sql".

I. referential integrity

Referential integrity refers to the design between multiple tables, mainly using foreign key constraints.

Multi-watch design: one-to-many, many-to-many, one-to-one design

1. One to many

Associate the main statement:

Constraint cus_ord_fk foreign key (customer_id) REFERENCES customer (id)

Create customer table-order table

A customer can place multiple orders, and each order can only have one customer.

-- Associate (1 pair of N) create table customer (id int PRIMARY KEY auto_increment, name varchar (20) not null, adress varchar (20) not null); create table orders (order_num varchar (20) PRIMARY KEY, price FLOAT not NULL, customer_id int,-- the field foreign key constraint cus_ord_fk foreign key (customer_id) REFERENCES customer (id) associated with customer) Insert into customer (name,adress) values ("zs", "Beijing"); insert into customer (name,adress) values ("ls", "Shanghai"); SELECT * from customer;INSERT INTO orders values ("010", 30.5); INSERT INTO orders values ("011,60.5); INSERT INTO orders values (" 012 ", 120.5); SELECT * from orders

Notice: constraint: the meaning of constraint. Foreign key: foreign key. References: referenc

Create a foreign key constraint called customer_id_fk, where the foreign key refers to customer_id and references the id column in the customers table

Extend:

Delete and update strategy for the word table:

1) CASCADE cascading policy. When using this strategy, the records of the primary table are deleted or the child tables are deleted or modified synchronously when the primary key field is modified.

2) NO ACTION has no action strategy. When using this strategy, to delete the primary table, you must first delete the child table. To delete the records of the primary table, you must first delete the records associated with the child table, and you cannot update the value of the primary table primary key field.

3) RSTRICT master table constraint strategy. This strategy has the same constraints on the primary table as NO ACTION does.

4) SET NO null policy. When using this strategy, if the primary table is deleted or the primary key is changed, the foreign key in the child table is set to NULL. It is important to note that if the foreign key of the child table is the primary key or is set to NOT NULL, the deletion of the primary table and the change of the primary key are the same as NO ACTION.

two。 Many to many

Create a student-teacher relationship table

One student can have more than one teacher, and one teacher can have more than one student.

-- many-to-many relationships-- create teacher forms CREATE TABLE teacher (id INT, NAME VARCHAR (20) NOT NULL, gender CHAR (2) NOT NULL, PRIMARY KEY (id));-- create student forms CREATE TABLE student (id INT, NAME VARCHAR (20) NOT NULL, age INT NOT NULL); ALTER TABLE student ADD PRIMARY KEY (id) -- the third relation table CREATE TABLE tch_std (teacher_id INT, student_id INT, CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (id), CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES teacher (id)); DELETEFROM teacherWHERE id = 3 position select * FROM teacher;SELECT DISTINCT * FROM tch_std

Attention: many to many are three tables. The third table creates foreign key constraints corresponding to the id in the first two tables.

3. one-for-one

There are two forms:

1) Associate according to foreign key

Add unique constraints to foreign keys in the IdCard table

2) Associate by primary key

Add a foreign key constraint to the primary key

-- one to one-- create users form CREATE TABLE users (id INT, NAME VARCHAR (20) NOT NULL, gender CHAR (2) NOT NULL, PRIMARY KEY (id));-- create card form CREATE TABLE card (id INT, adress VARCHAR (100) NOT NULL, user_id INT UNIQUE, constraint users_id_fk foreign key (user_id) references users (id)) ALTER TABLE card ADD PRIMARY KEY (id)

This is method 1, which adds a unique constraint unique to the foreign key

The above is all the content of the article "sample Analysis of referential Integrity in sql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.