SQL PRIMARY key constraint \sql FOREIGN key constraint \sql CHECK constraint

SQL PRIMARY KEY Constraint

The 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.

SQL PRIMARY KEY Constraint on CREATE TABLE

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) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), PRIMARY KEY (Id_P) )

SQL Server/oracle/ms Access:

PRIMARY KEY, LastName varchar (255) 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) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName) )

SQL PRIMARY KEY Constraint on ALTER TABLE

If you 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)

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:

ALTER TABLE PersonsADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

Note: If you use the ALTER table statement to add a primary key, you must declare the primary key column to not contain a NULL value (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

SQL FOREIGN KEY Constraint

The FOREIGN key in one table points to the PRIMARY key in the other table.

Let's use an example to explain the foreign key. Take a look at the following two tables:

"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

Notice that 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.

The FOREIGN KEY constraint is used to prevent actions that disrupt the connection 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.

SQL FOREIGN KEY Constraint on CREATE TABLE

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 KEY (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 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:

CREATE TABLE Orders (id_o int not null,orderno int not null,id_p int,primary KEY (id_o), CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)REFERENCES Persons(Id_P) )

SQL FOREIGN KEY Constraint on ALTER TABLE

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 FOREIGN KEY Constraint

The FOREIGN key in one table points to the PRIMARY key in the other table.

Let's use an example to explain the foreign key. Take a look at the following two tables:

"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

Notice that 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.

The FOREIGN KEY constraint is used to prevent actions that disrupt the connection 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.

SQL FOREIGN KEY Constraint on CREATE TABLE

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 KEY (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 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:

CREATE TABLE Orders (id_o int not null,orderno int not null,id_p int,primary KEY (id_o), CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)REFERENCES Persons(Id_P) )

SQL FOREIGN KEY Constraint on ALTER TABLE

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 CHECK constraints

A CHECK constraint is used to limit the range of values in a column.

If you define a CHECK constraint on a single column, the column only allows a specific value.

If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint for the "id_p" column when the "Persons" table is created. The CHECK constraint specifies that the "id_p" column must contain only integers greater than 0.

My SQL:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CHECK (Id_P>0) )

SQL Server/oracle/ms Access:

CHECK (Id_P>0), LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

If you need to name a check constraint and define a CHECK 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) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes‘) )

SQL CHECK Constraint on ALTER TABLE

If you create a CHECK constraint for the "id_p" column if the table already exists, use the following SQL:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CHECK (Id_P>0)

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

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes‘)

Revoke a CHECK constraint

To revoke a check constraint, use the following SQL:

SQL Server/oracle/ms Access:

ALTER TABLE PersonsDROP CONSTRAINT chk_Person

Mysql:

ALTER TABLE PersonsDROP CHECK chk_Person

• SQL Foreign Key
• SQL Default

SQL PRIMARY key constraint \sql FOREIGN key constraint \sql CHECK constraint