(
cot1 kieu_du_lieu [ NULL | NOT NULL ] [ PRIMARY KEY ],
cot2 kieu_du_lieu [ NULL | NOT NULL ],
.
);
or
CREATE TABLEten_bang
(
cot1 kieu_du_lieu [ NULL | NOT NULL ],
cot2 kieu_du_lieu [ NULL | NOT NULL ],
…
CONSTRAINT ten_rang_buoc PRIMARY KEY (cot1, cot2, … cot_n)
);
The primary key generation example is with the CREATE TABLE command
CREATE TABLE nhanvien
( nhanvien_id INT PRIMARY KEY,
ho VARCHAR(50) NOT NULL,
ten VARCHAR(50), NOT NULL,
luong MONEY
);
In this example, the primary key for the nhanvien table is made up of a field called nhanvien_id. In addition, the primary key can be created as follows:
CREATE TABLE nhanvien
( nhanvien_id INT,
ho VARCHAR(50) NOT NULL,
ten VARCHAR(50), NOT NULL,
luong MONEY
CONSTRAINT nhanvien_pk PRIMARY KEY (nhanvien_id)
);
Now is the example of creating a primary key with more than 1 field in SQL Server.
CREATE TABLE vnien
( ho VARCHAR(50) NOT NULL,
ten VARCHAR(50), NOT NULL,
luong MONEY
CONSTRAINT nhanvien_pk PRIMARY KEY (ho, ten)
);
In this example, we created the primary key made up of two columns, ho
and ten
. These two fields will uniquely identify the record in the table.
Primary keys can only be created with the ALTER TABLE command in columns defined as NOT NULL. If the column allows NULL values, the primary key cannot be added without deleting or recreating the table.
The syntax for creating a primary key is with the ALTER TABLE command
ALTER TABLE ten_bang
ADD CONSTRAINT t
en_rang_buoc PRIMARY KEY (cot1, cot2, . cot_n);
Example of creating a primary key with the ALTER TABLE command
ALTER TABLE nhanvien
ADD CON
STRAINT nhanvien_pk PRIMARY KEY (nhanvien_id);
In this example, we create the primary key for the available table, including the nhanvien_id field . Note that the nhanvien_id field must be defined as NOT NULL before, otherwise it will have to be deleted and re-created the table and define this field as NOT NULL.
You can create a primary key with more than 1 field as in the following example.
ALTER TABLE staff
ADD CONSTRAINT n
hanvien_pk PRIMARY KEY (cough, ten);
The primary key for the table includes 2 fields, namely first and last name. Both must be defined as NOT NULL.
The syntax for deleting the primary key is with the ALTER TABLE command
ALTER TABLE ten_bang
DROP CONS
TRAINT ten_rang_buoc;
Example of deleting the primary key with the ALTER TABLE command
ALTER TABLE staff
DROP CONSTRAINT
nhanvien_pk;
The above example deletes the primary key in the table.
The syntax to disable the primary key with the ALTER INDEX command
ALTER INDEX ten_rang_buoc ON ten_bang
DISABLE;
For example, disable the primary key with the ALTER INDEX command
ALTERINDEX nhanvien_pk ON nhanvien
DISABLE
;
The example above disables the primary key in the table.
The syntax for activating the primary key is with the ALTER INDEX command
ALTER INDEX ten_rang_buoc ON ten_bang
REBU
ILD;
For example, activate the primary key with the ALTER INDEX command
ALTER INDEX nhanvien_pk ON nhanvien
REBUI
LD;
The above example re-activates the primary key of the table.
Previous article: CREATE TABLE command in SQL Server
The following article: Alter Table command in SQL Server