SQL Advanced Application-constraint two--(PRIMARY key, FOREIGN key)

Iv. SQL PRIMARY KEY constraint

PRIMARY KEY constraint uniquely identifies each record in a database table

The primary key must contain a unique value

Primary key columns cannot contain NULL values

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

The following SQL creates the primary KEY constraint in the id_p column when the Persons table is created

Mysql

CREATE TABLE Persons (    id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255)    PRIMARY KEY (id_p))

SQL Server/oracle/ms Access

CREATE TABLE Persons (    id_p int not null PRIMARY KEY,    LastName  varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255))

If you need to name the PRIMARY key constraint and define the PRIMARY key constraint for more than one column, use the following SQL syntax

Mysql/sql Server/oracle/ms Access

CREATE TABLE Persons (    id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255),    CONSTRAINT pk_personid PRIMARY KEY (id_p, LastName))

　　

To create a PRIMARY KEY constraint for the id_p column if the table already exists, use the following SQL

Mysql/sql Server/oracle/ms Access

ALTER TABLE personsadd PRIMARY KEY (id_p)

　　

To name the PRIMARY key constraint and define the PRIMARY key constraint for multiple columns, use the following SQL

Mysql/sql Server/oracle/ms Access

ALTER TABLE personsadd CONSTRAINT pk_personid PRIMARY KEY (id_p,lastname)

If you use the ALTER table statement to add a primary key, you must declare the primary key column as a value that does not contain null (when the table is first created)

　　

Revoke PRIMARY KEY Constraint

To revoke the PRIMARY KEY constraint, use the following SQL

Mysql

ALTER TABLE Personsdrop PRIMARY KEY

SQL Server/oracle/ms Access

ALTER TABLE Personsdrop CONSTRAINT Pk_personid

　　

V. SQL FOREIGN KEY Constraint

FOREIGN key in one table points to PRIMARY key in another table

There are two of tables below

"Persons" table:

City

id_p	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" table:

Id_o	OrderNo	id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1

Note: The id_p column in orders points to the Id_p column in the Persons table

The id_p column in the Persons table is the PRIMARY KEY in the Persons table

The id_p column in the Orders table is the FOREIGN KEY in the Orders table

FOREIGN key constraints are used to prevent the action of breaking connections between tables

The FOREIGN key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to

　　

The following SQL creates FOREIGN KEY for the id_p column when the Orders table is created

Mysql

CREATE TABLE Orders (    id_o int not NULL,    OrderNo int. NOT NULL,    id_p int,    PRIMARY KEY (id_o),    FOREIGN KE Y (id_p) REFERENCES Persons (id_p))

SQL Server/oracle/ms Access

CREATE TABLE Orders (    id_o int not null PRIMARY key,    OrderNo int not NULL,    id_p int FOREIGN KEY REFERENCE Pers ONS (id_p))

　　

If you need to name the FOREIGN key constraint and define the FOREIGN key constraint for multiple columns, use the following SQL

Mysql/sql Server/oracle/ms Access

CREATE TABLE Orders (    id_o int not NULL,    OrderNo int. NOT NULL,    id_p int,    PRIMARY KEY (id_o),    constrain T fk_perorders FOREIGN KEY (id_p) REFERENCES Persons (id_p))

　　

If you create a FOREIGN KEY constraint for the id_p column if the Orders table already exists, use the following SQL

Mysql/sql Server/oracle/ms Access

ALTER TABLE ordersadd FOREIGN KEY (id_p) REFERENCES Persons (id_p)

If you need to name the FOREIGN key constraint and define the FOREIGN key constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE ordersadd CONSTRAINT fk_perordersforeign KEY (id_p) REFERENCES Persons (id_p)

　　

Revoke FOREIGN KEY Constraint

To revoke the FOREIGN KEY constraint, use the following SQL:

Mysql:

ALTER TABLE ordersdrop FOREIGN KEY fk_perorders

　　SQL Server/oracle/ms Access:

ALTER TABLE Ordersdrop CONSTRAINT fk_perorders

　　

SQL Advanced Application-constraint two--(PRIMARY key, FOREIGN key)