SQL primary key constraints and foreign key constraints, SQL primary key constraints

SQL primary key constraints and foreign key constraints, SQL primary key constraints
Primary key constraint SQL PRIMARY KEY constraint

The primary key constraint uniquely identifies each record in the database table.

The primary key must contain a unique value.

The primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only one primary key.

SQL PRIMARY KEY constraints for CREATE TABLE

The following SQL statement creates a PRIMARY KEY constraint on the "P_Id" column when creating the "Persons" table:

MySQL:

CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id))

SQL Server/Oracle/MS Access:

CREATE TABLE Persons(P_Id int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

Multiple primary key constraints

To name the primary key constraint and define the primary key constraint for multiple columns, use the following SQL Syntax:

For MySQL/SQL Server/Oracle/MS Access:

CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName))

Note: In the above instance, there is only one primary key (pk_PersonID ). However, the value of pk_PersonID is composed of two columns (P_Id and LastName.

Foreign key constraint SQL FOREIGN KEY constraint

The foreign key in one table points to the primary key in another table.

Let's use an instance to explain the foreign key. See the following two tables:

"Persons" table:

"Orders" table:

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the primary key in the "Persons" table.

The "P_Id" column in the "Orders" table is the foreign key in the "Orders" table.

The foreign key constraint is used to prevent table connection corruption.

The foreign key constraint can also prevent illegal data insertion into the foreign key column because it must be one of the values in the table to which it points.

SQL FOREIGN KEY constraints for CREATE TABLE

The following SQL statement creates a FOREIGN KEY constraint on the "P_Id" column when creating the "Orders" table:

MySQL:

CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,P_Id int,PRIMARY KEY (O_Id),FOREIGN KEY (P_Id) REFERENCES Persons(P_Id))

SQL Server/Oracle/MS Access:

CREATE TABLE Orders(O_Id int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,P_Id int FOREIGN KEY REFERENCES Persons(P_Id))

Define multiple foreign keys

To name the foreign key constraint and define the foreign key constraint for multiple columns, use the following SQL Syntax:

For MySQL/SQL Server/Oracle/MS Access:

CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,P_Id int,PRIMARY KEY (O_Id),CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)REFERENCES Persons(P_Id))

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger. Http://blog.csdn.net/yan3013216087/article/details/79082558