语法:(主键是系统自动生成)
create table table_name(
colunm_name datatype primary key,.....
)
示例;
create table userinfo_p
(
id number(6,0) primary key,
name varchar2(20),
password varchar2(20)
)
在创建表时设置联合主键约束
语法:
constraint constraint_name primary key(column_name,...)
示例:
create table userinfo
(
id number(6,0),
username varchar2(20),
password varchar2(20),
constraint pk_id_username primary key(id,username)
);
查看主键约束:
user_constraints(数据字典)
desc user_constraints
select constraint_name from user_constraints where table_name='USERINFO';(表名要大写的)
在修改表时添加主键约束:(最好表没数据,如果有数据,id的数据为null,不唯一。会报错的)
语法:
alter table tablename add constraint constraint_name primary key(column_name,..);
示例:
alter table userinfo add constraint pk_id primary key(id);
更改主键约束的名字:
语法:
alter table tablename rename constraint old_name to new_name
示例:
alter table userinfo rename constraint pk_id to pk_idf;
查看主键约束:
user_constraints(数据字典)
desc user_constraints
select constraint_name from user_constraints where table_name='USERINFO';(表名要大写的)
删除主键约束:
语法:
alter table tablename disable|enable constraint constraint_name;(disable:禁用,enable:启用)
alter table tablename drop constraint constraint_name;(删除方法一)
alter table tablename dropprimary key;(删除方法二)
alter table tablename drop primary key cascade;(级联也删除)
示例:
alter table userinfo disable constraint pk_id;
alter table userinfo enable constraint pk_id;
alter table userinfo drop constraint pk_id;
alter table userinfo drop primary key (cascade);
查看主键状态:(status:状态)
select constraint_name,status from user_constraints where table_name='USERINFO';
在创建表时设置主键约束:(列级)
语法:(table1:从表,table2:主表,主表字段必须是主键,主从表的相应字段必须是同一类型的字段)
create table table1
(
column_name datatype references
table2(column_name),...
);
示例:
主表:
create table typeinfo
(
typeid varchar2(10) primary key,
typename varchar2(20)
);
从表:
create table userinfo
(
id varchar2(10) primary key,
username varchar2(20),
typeid1 varchar2(10) references typeinfo(typeid)
);
注意:从表中typeid1中的值必须是主表typeid的值或者是null;否则insert into(插入会报错的)
在创建表时设置主键约束:(列级)
语法:
constraint constraint_name foreign key(column_name)
references table_name(column_name) [on delete cascade](on delete cascade是级联操作)
示例:
主表:
create table typeinfo
(
typeid varchar2(10) primary key,
typename varchar2(20)
);
从表:
create table userinfo
(
id varchar2(10) primary key,
username varchar2(20),
typeid1 varchar2(10),
constraint fk_typeid_1 foreign key(typeid1) references typeinfo(typeid)
);
从表:(有级联delete操作的)
create table userinfo
(
id varchar2(10) primary key,
username varchar2(20),
typeid1 varchar2(10),
constraint fk_typeid_1 foreign key(typeid1) references typeinfo(typeid)
on delete cascade
);
在修改表添加表外键约束:语法:
alter tablename add constraint constraint_name foreign key(column_name)
references table_name(column_name) [on delete cascade]
示例:
alter userinfo add constraint fk_typeid_1 foreign key(typeid1) references typeinfo(typeid);
alter userinfo add constraint fk_typeid_1 foreign key(typeid1) references typeinfo(typeid) on delete cascade;(带有级联delete)
删除外键约束:
语法:
alter table tablename disable|enable constraint constraint_name;(disable:禁用,enable:启用)
alter table tablename drop constraint constraint_name;(删除方法一)
示例:
alter table userinfo disable constraint fk_typeid_1;
alter table userinfo enable constraint fk_typeid_1;
alter table userinfo drop constraint fk_typeid_1;