约束的机制包括这几点:非空(NOT NULL)约束、检查(CHECK)约束、唯一(UNIQUE)约束、主键(PRIMARY KEY)约束、外键(FOREIGN KEY)约束
1.主外键(PRIMARY KEY & FOREIGN KEY)约束
1.1创建category主表
- create table category(
- c_id int, /*新闻类别编号*/
- c_name char(50), /*新闻类别名称*/
- constraint PK_c_id primary key(c_id) /*设置新闻类别编号c_id为主键*/
- )
1.2插入主表的数据
- insert into category values('1000','国际新闻');
- insert into category values('1001','国内新闻');
- insert into category values('1002','体育新闻');
- insert into category values('1003','财经新闻');
- insert into category values('1004','军事新闻');
- insert into category values('1005','经济新闻');
- select * from category;
1.3创建news从表
- create table news(
- n_id int, /*新闻编号*/
- c_id int, /*新闻所属类别编号*/
- n_pic char(2), /*是否有图片*/
- n_readcount int, /*新闻点击次数*/
- constraint PK_n_id primary key(n_id), /*设置新闻编号为主键*/
- constraint FK_c_id foreign key(c_id) references category(c_id) /**/
- )
1.4插入从表的数据
- insert into news values('010','1000','有','24');
- insert into news values('011','1001','无','30');
- insert into news values('012','1002','有','32');
- insert into news values('013','1003','有','21');
- insert into news values('014','1004','无','34');
- insert into news values('015','1005','有','212');
- insert into news values('016','1003','有','22');
- select * from news;
1.5主从表的关系图