81 lines
2.6 KiB
SQL
81 lines
2.6 KiB
SQL
/*
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS `product`;
|
|
DROP TABLE IF EXISTS `pkg`;
|
|
DROP TABLE IF EXISTS `file`;
|
|
DROP TABLE IF EXISTS `product_and_pkg`;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
|
|
ON DELETE 和 ON UPDATE,表示当发生delete和update时,会发生什么行为
|
|
|
|
NO ACTION:默认的,表示没有什么行为.
|
|
RESTRICT:当有一个child关联到parent时,禁止delete或update parent
|
|
SET NULL:当parent被delete或update时,child的的关联字段被置为null(如果字段有not null,就出错)
|
|
SET DEFAULT:类似于SET NULL (是不是设置默认值?没有试过)
|
|
CASCADE:将实施在parent上的删除或更新操作,传播给你吧与之关联的child上.
|
|
对于 ON DELETE CASCADE, 同被删除的父表中的行 相关联的子表中的每1行,也会被删除.
|
|
对于ON UPDATE CASCADE, 存储在子表中的每1行,对应的字段的值会被自动修改成同新的父键匹配
|
|
|
|
|
|
DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。
|
|
FOREIGN KEY (`product_id`) REFERENCES product(`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (`pkg_id`) REFERENCES pkg(`pkg_id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
|
|
CREATE TABLE `pkg_and_file` (
|
|
`paf_id` INTEGER NOT NULL,
|
|
`pkg_id` INTEGER NOT NULL,
|
|
`file_id` INTEGER NOT NULL,
|
|
PRIMARY KEY (`paf_id`)
|
|
);
|
|
*/
|
|
|
|
CREATE TABLE `product` (
|
|
`product_id` SMALLINT NOT NULL,
|
|
`product_name` TEXT(255) NOT NULL,
|
|
`product_version` CHAR(255) NOT NULL,
|
|
`product_md5` CHAR(32) NOT NULL,
|
|
`product_issue` CHAR NOT NULL,
|
|
PRIMARY KEY (`product_id`)
|
|
);
|
|
|
|
CREATE TABLE `pkg` (
|
|
`pkg_id` INTEGER NOT NULL,
|
|
`pkg_name` CHAR(255) NOT NULL,
|
|
`pkg_version` CHAR(255) NOT NULL,
|
|
`pkg_md5` CHAR(32) NOT NULL,
|
|
PRIMARY KEY (`pkg_id`)
|
|
);
|
|
|
|
CREATE TABLE `file` (
|
|
`file_id` INTEGER NOT NULL,
|
|
`file_name` char(255) NOT NULL,
|
|
`pkg_id` INTEGER NOT NULL,
|
|
`file_pathname` CHAR(4096) NOT NULL,
|
|
`file_md5` CHAR(32) DEFAULT " ",
|
|
`file_type` CHAR NOT NULL,
|
|
`file_owner` CHAR NOT NULL,
|
|
`file_genus` CHAR NOT NULL,
|
|
`file_permis` INTEGER NOT NULL,
|
|
`file_size` INTEGER NOT NULL,
|
|
`file_acl` CHAR DEFAULT " ",
|
|
`file_capable` CHAR DEFAULT " ",
|
|
`file_level` INTEGER DEFAULT 0,
|
|
`file_mac` CHAR DEFAULT " ",
|
|
PRIMARY KEY (`file_id`)
|
|
);
|
|
|
|
CREATE TABLE `product_and_pkg` (
|
|
`pap_id` INTEGER NOT NULL,
|
|
`product_id` SMALLINT NOT NULL,
|
|
`pkg_id` INTEGER NOT NULL,
|
|
PRIMARY KEY (`pap_id`)
|
|
);
|
|
|
|
CREATE TABLE `pkg_and_file` (
|
|
`paf_id` INTEGER NOT NULL,
|
|
`pkg_id` INTEGER NOT NULL,
|
|
`file_id` INTEGER NOT NULL,
|
|
PRIMARY KEY (`paf_id`)
|
|
);
|