管理如何插入或处理数据库数据的规则。
DBMS 通过在数据库表上施加约束来实施引用完整性。
大多数约束是在 表定义中定义的,用 CREATE TABLE 或 ALTER TABLE 语句。
主键是一种特殊的约束,用来保证一列(或 一组列)中的值是唯一的,而且永不改动。主键可以唯一标识表中的一行。且主键值不能重用。如果从表中删除某一行,其主键值不分配给新行。
这方便了直接或交互地处理表中的行。没有主键,要安全地 UPDATE 或 DELETE 特定行而不影响其他行会 非常困难。
CREATE TABLE `supplier_new` (
`id` INTEGER NOT NULL,
`supplier` TEXT,
`supplierAddress` TEXT,
`supplierTel` TEXT NOT NULL,
`supplierEmail` TEXT,
`supplierContact` TEXT,
PRIMARY KEY(`id`)
);
CREATE TABLE `adjunctlist` (
`id` integer PRIMARY KEY AUTOINCREMENT,
`add_time` text,
`ext0` text,
`ext1` text,
`file_name` text,
`file_no` integer,
`file_path` text,
`file_uri` text,
`flaw_id` integer,
`is_upload` integer,
`local_flaw_id` integer,
`user_id` text,
`task_id` integer,
`task_point_id` integer,
`task_type` integer
);
ALTER TABLE 表名 ADD CONSTRAINT PRIMARY KEY (列名);
比如这张表在创建的时候没有指定主键,我们可以通过ALTER TABLE语句为其指定主键
alter table test
add constraint primary key (test.no)
**注意:**SQLite不允许使用ALTER TABLE定义键,要求在初始的CREATE TABLE 语句中定义它们。
外键是表中的一列,其值必须是另一表的唯一约束列,一般用另外一张表的主键。
当我们指定外键后,DBMS 不允许删除在另一个表中具有关联行的行,因此,可以利用外键防止意外删除。
比如,我们我们的订单表( oderlist_new )和供应商表( supplier_new )
REFERENCES 表名(列名)
CREATE TABLE `oderlist_new` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`goodsName` TEXT,
`quantity` integer,
`item_price` real,
`orderNo` text NOT NULL,
`userId` INTEGER,
`userName` TEXT,
`orderTime` TEXT,
`supplierId` INTEGER NOT NULL REFERENCES supplier_new (id)
);
ALTER TABLE 表1
ADD CONSTRAINT FOREIGN KEY (表1.列) REFERENCES 表2(表2.列)
delete from supplier_new
where id = 1
唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主 键,但存在以下重要区别。
1、表可包含多个唯一约束,但每个表只允许一个主键。
2、唯一约束列可包含 NULL 值。
3、唯一约束列可修改或更新。
4、唯一约束列的值可重复使用。
5、与主键不一样,唯一约束不能用来定义外键。
例如我们的订单表,id是主键(唯一约束),订单编号orderNo也是唯一约束
唯一约束既可以用 UNIQUE 关 键字在表定义中定义,也可以用单独的 CONSTRAINT 定义。
创建订单表的时候,指定orderNo具有唯一约束orderNo text NOT NULL UNIQUE
CREATE TABLE `oderlist_new` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`goodsName` TEXT,
`quantity` integer,
`item_price` real,
`orderNo` text NOT NULL UNIQUE,
`userId` INTEGER,
`userName` TEXT,
`orderTime` TEXT,
`supplierId` INTEGER NOT NULL,
FOREIGN KEY(`supplierId`) REFERENCES `supplier_new`(`id`)
);
检查约束用来保证一列(或一组列)中的数据满足一组指定的条件。
1、检查最小或最大值。例如,防止 0个物品的订单(即使0是合法的数)。
2、指定范围。例如,保证发货日期大于等于今天的日期,但不超过今天 起一年后的日期。
3、只允许特定的值。例如,在性别字段中只允许 M 或 F。
例如,我们限定订单表中商品单价和商品数量必须大于0,CHECK(quantity > 0)
和 CHECK(item_price > 0)
CREATE TABLE `oderlist_new` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`goodsName` TEXT,
`quantity` integer CHECK(quantity > 0),
`item_price` real CHECK(item_price > 0),
`orderNo` text NOT NULL UNIQUE,
`userId` INTEGER,
`userName` INTEGER,
`orderTime` TEXT,
`supplierId` INTEGER NOT NULL,
FOREIGN KEY(`supplierId`) REFERENCES `supplier_new`(`id`)
);
这样我们在插入不符合要求的数据时就会失败,比如我们插入如下数据
"菠萝"
"0"
"0.0"
"20181023008"
"30"
"王舍"
"2018-10-23 23:12:49"
3
insert into oderlist_new (goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
VALUES ('菠萝',0,0.0,'20181023008',30,'王舍','2018-10-23 23:12:49',3)
insert into oderlist_new (goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
VALUES ('菠萝',10,20.0,'20181023008',30,'王舍','2018-10-23 23:12:49',3)
索引用来排序数据以加快搜索和排序操作的速度。
注意
1、索引改善检索操作的性能,但降低了数据插入、修改和删除的性能。 在执行这些操作时,DBMS必须动态地更新索引。
2、索引数据可能要占用大量的存储空间。
所以,在这些情况下不应该使用索引
索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。
创建索引
索引用CREATE INDEX
语句创建(不同DBMS创建索引的语句变化很大)。
CREATE INDEX 索引名
ON
表名 (列名);
例如,我们用订单编号作为订单表的索引
CREATE INDEX oderlist_new_ind
ON
oderlist_new (orderNo);
SELECT *
FROM sqlite_master
WHERE type = 'index';
SELECT *
FROM sqlite_master
WHERE type = 'index' and tbl_name = 'oderlist_new';
DROP INDEX 索引名
DROP INDEX 'oderlist_new_ind';