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

Source: Internet
Author: User
Tags sql primary key

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.