The foreign key in the table restricts the field corresponding to the attribute and restricts the data of the attribute inserted into the table. And the foreign key is mapped to the primary key of the corresponding table that can uniquely identify each piece of data. According to the reference integrity rules, the foreign key can take a null value or the value of the corresponding primary key. The function of foreign keys can be considered to produce a certain relationship between the two tables. The following describes how to set foreign key constraints

        1. Set foreign key constraints when creating tables.

        In order to insert non-standard data and report errors faster, foreign keys generally set an alias to represent foreign key constraints. The attribute of another table referenced by the foreign key is a primary key.

        We can directly set the foreign key constraint on the right side of the declaration attribute when creating the table, like writing the primary key, unique, not null constraints. At this time, there is no need to write a foreign key to indicate that the constraint is a foreign key constraint.

Format is

create table xxx(

        Property name property type constraint foreign key name references primary key table (primary key name)

);

        Let's demonstrate that there is a student table, in which pid is the primary key, indicating the student's student number, and pname is the student's name. The other table is s_ The course table is used to represent the courses selected by students, where pid represents the student number corresponding to the student. pid in the student table is the foreign key of the table, and the other cname represents the course name.

        

--Student list
create table student(
       pid varchar2(10) primary key,
       pname varchar2(10)
       
);
--Students choose courses
create table s_course(
       pid varchar(10) constraint FK_pid references student(pid),
       cname varchar(20)
);

        We insert data into its two tables

insert into student values('A001','I');
insert into s_course value ('A001','Database overview');

        It is found that the above sql statements can be executed successfully.

        However, when we insert the curriculum, the inserted student number is not empty and the student number in the student table does not have a value, an error will be reported.

insert into s_course values('A002','software engineering');

        Another way to set constraints in the table is to write the foreign key constraints at the end of the table after all the attributes in the table are declared. At this time, you need to specify the foreign key.

        The format is:

        create table xxx(

                .....    ,

                 constraint foreign key name foreign key references primary key table  

        );

          Let's also demonstrate the effect, such as the student table and s above_ We modify the course table.

        

drop table student;
drop table s_course;
--Student list
create table student(
       pid varchar2(10) primary key,
       pname varchar2(10)
       
);

--Students choose courses
create table s_course(
       pid varchar(10) ,
       cname varchar(20),
       constraint FK_pid foreign key (pid) references student(pid) 
);

        There is no problem inserting data.

insert into student values('A001','I');
insert into s_course values ('A001','Database overview');

        2. Set foreign key constraints outside the table.

        After we create the table, we find that we need to have a foreign key constraint. On the premise of not rebuilding the table, we can add the constraint condition of this attribute through the alter statement. Foreign keys are also constraints.

        The format is:

        alter table foreign key table name add constraint foreign key name foreign key references primary key table name

        The following is a demonstration of the effect. This example also uses the above student table and s_course table.

         

drop table student;
drop table s_course;
--Student list
create table student(
       pid varchar2(10) primary key,
       pname varchar2(10)
       
);
--Students choose courses
create table s_course(
       pid varchar(10),
       cname varchar(20)
);

alter table s_course add constraint FK_pid foreign key (pid ) references student(pid);

        The insert data test can also pass.

insert into student values('A001','I');
insert into s_course values ('A001','Database overview');