Foreign key constraint

Foreign key constraints are a means to ensure the correctness of data in the table. They are often used together with primary key constraints. The foreign key constraint is used to constrain data consistency in two tables.

1. The foreign key constraint syntax is as follows:

"Alter table table_name

Column_name1datatype,

Column_name2 datatype,

Column_name3 datatype

...

Constraint fk_name foreign key (List 1) References table_name1 (List 2)

); "* Here, fk_name is the name of the foreign key constraint, column name 1 is the column that sets the foreign key constraint, table_name1 is the name of the parent table, and column name 2 is the primary key column of the parent table.

[Example 2] Create a room type information table (typeinfo), create a foreign key constraint for the room type column in the room type information table (roominfo.

No.	Data Type	Constraint 1	Interger	Room Type
Roomtype	20)	<Span font-style: normal; "=" "style =" word-wrap: Break-word; ">

The code for creating the room type information table is as follows:

"Create Table typeinfo

(

Roomtypeid int PEIMARY key

Roomtype vachar (20)

);"

 

The code for adding a foreign key constraint to the room type (roomtype) column when you create a room information table (roominfo) is as follows:

"Create Table typeinfo

(

Roomid int primary key,

Roomtypeid int,

Roomprice numeric (7,2 ),

Roomstate varchar (2 ),

Pemark varchar (200 ),

Constraint pk_roominfo foreign key (roomid) refferences

Typeinfo (roomtypeid)

);"

Add a foreign key constraint when modifying a table

The foreign key constraint can also be added when the table is modified. However, the premise of adding a foreign key constraint is that the data in the column that sets the foreign key constraint must be consistent with the field in the referenced primary key table or there is no data.

The syntax for adding a foreign key constraint when modifying a table is as follows:

"Create Table table_name

Add constraint fk_name foreign key (column name 1) References table_name2 (List 2) "* Here, fk_name is the name of the foreign key constraint, and column 1 is the column with foreign key constraints set in table_name1; table_name2 is the name of the parent table, and column name 2 is the primary key column in the parent table.

[Example 1] assuming that the room information table (roominfo) already exists, you need to add a foreign key constraint for the room type Number Column (roomtypeid) in the room information table (roominfo, make it reference the room type Number Column (roomtypeid) in the room type table (typeinfo ).

The Code is as follows:

"Alter table roominfo

Add constraint fk_name roomtypeid forrign key (roomtypeid) refereces

Typeinfo (roomtypeid )"

Delete foreign key constraints

When a table does not require foreign key constraints, it needs to be deleted from the table. Deleting a foreign key constraint is easier than creating a foreign key constraint. The syntax for deleting a foreign key constraint is as follows:

"Alter table table_name

Drop foreign key fk_name ;"

Here, fk_name is the name of the foreign key constraint. The following example shows how to delete the primary key constraint.

[Example 1] Delete the foreign key constraint fk_roomtypeid in the room information table (roominfo.

"Alter table roominfo

Drop foreign key fk_roomtypeid ;"