【转】SQL语句删除和添加外键、主键

by admin on 2020年1月31日

【求助】SQL2000如何修改表的属性

1:用户和权限

–删除外键

1.在SQL Server 2000的查询分析器里建表时偶然遇到一个问题:
点击SQL Server
2000的查询分析器,进入刚建立的一个数据库SPJ里,在当下使用如下命令建

1.1 用户的创建

语法:alter table 表名 drop constraint 外键约束名

立一个表spj:
create table spj
(sno char(4),
 pno char(4),
 jno char(4),
 qty int,
 primary key (sno,pno),
 foreign key (sno) references s (sno),
 foreign key (pno) references p (pno),
 foreign key (jno) references j (jno)
)

a)语法—   
create user 用户名  identified by
密码;

如:

外码sno、pno、jno分别参照已建好的表s、p、j。
之后插入数据命令时:
insert
into spj(sno,pno,jno,qty)
s(‘s1′,’p1′,’j3’,100) /*之前输入一个值s(‘s1′,’p1′,’j1’,200)*/

b)创建用户abcd,并设定密码为abcd;—注意:操作数据库对象是需要DBA(数据库管理员)权限的;

alter table Stu_PkFk_Sc drop constraint FK_s

执行命令后显示:
违反了 PRIMARY KEY 约束 ‘PK__spj__1CF15040’。不能在对象 ‘spj’
中插入重复键。
语句已终止。

create user abcd
identified by bjsxt;

alter table Stu_PkFk_SC drop constraint FK_c

这是因为我建表spj时设定(sno,pno)为主码,所以输入第二个值时违反了列级完整性约束条

1.2
给用户授权

 

件。

Oracle中的权限很多,为了方便用户管理权限,提供了角色这个概念。角色代表一个身份,该身份拥有固定的权限。常见的角色:DBA(数据库管理员),CONNECT(临时用户,拥有少量的权限),RESOURCE(比较靠谱的权限,拥有更多的权限,不能管理数据库);

–添加外键

故我想起alter命令来修改表的属性,只是课本说的很不详细,我试了几个命令格式都不行:

a)给abcd用户授予普通用户的权限

语法:alter table 表名 add constraint 外键约束名 foreign key(列名)
references 引用外键表(列名)

alter table spj
drop (sno,pno) primary key
add (sno,pno,jno) primary key  /*语法错误*/

grant
connect,resource to abcd;

如:

或者
alter table spj
drop primary key (sno,pno)
add primary key (sno,pno,jno) /*在关键字 ‘primary’ 附近有语法错误*/

b)从abcd用户撤销resource权限

alter table Stu_PkFk_Sc

或者
alter table spj
add primary key(sno,pno,jno)
/*分析命令通过,执行命令显示:
已在表 ‘spj’ 上定义了主键。
未能创建约束。请参阅前面的错误信息。*/

revoke resource
from abcd;

add constraint Fk_s

我的意思就是想修改表的约束条件,应如何修改呢?

1.3 删除用户

foreign key (sno)

 

drop user
abcd;

references Stu_PkFk_S(sno)

1.4修改用户

go

1.4.1修改用户密码

 

alter user abcd
identified by 123;

–删除主键

1.4.2锁定用户和解锁用户

语法:alter table 表名 drop constraint 主键约束名

a)锁定用户

如:

alter user abcd
account lock;

alter table Stu_PkFk_S drop constraint PK_S

b)解锁用户

go

alter user abcd
account unlock;

 

2.数据库表

–增加主键

2.1Oracle中的数据类型

语法:alter  表名 add  constraint  主键约束名 primary key(列名)

2.1.1字符类型

alter table Stu_PkFk_S add constraint PK_S primary key (sno)

a)varchar2—可变长度的字符串

go

b)cahr      
—不可变长度的字符串,效率较高;

图形化操作参考:

2.1.2数值类型

number,既可以表示整数,也可以表示浮点数;

2.1.3日期时间类型

a)date,存放日期和时间;

b)timestamp,比date更精确的日期时间类型;

2.1.4 
lob类型

a)blob,用于存放二进制数据,可以用于存放文件、图片、音频、视频等二进制数据;

b)clob,用于存放答文本信息;

2.2创建表格

创建学生表,字段包含学号、姓名、性别、年龄、入学日期、班级、email等信息;

create table
student(

        sno
number(4),

        sname
varchar(12),

        gender
char(3),

        age
number(3),

        sdate
date,

        clazz
varchar2(20),

        email
varchar2(50)

)

2.3修改表格

2.3.1添加字段

alter table
student add(score number(3));

2.3.2修改字段的类型

alter table
student modify(score number(5,2));

2.3.3重命名字段

alter table
student rename column score to fenshu ;

2.3.4删除字段

alter table
student drop column fenshu;

2.3.5删除表格

drop table
stu;

3.表格的约束

constraints,约束,用于对表格的额数据进行限制,保证表格数据的完整性和一致性。

语法:constraints
约束名+约束类型(约束字段)

3.1主键约束(primary
key)

主键约束用于唯一标识一条记录的字段,必须保证即非空有唯一。

一张表中,只能有一个主键;

a)在表级别定义主键约束,对约束统一管理

create table student (

       sno number(4),

       sname varchar2(12),

       gender char(3),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50),

       constraints pk_student primary key
(sno)

);

b)在列级别添加主键约束

create table student (

       sno number(4) constraints
pk_student primary key,

       sname varchar2(12),

       gender char(3),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50)

);

c)在列级别添加简化版约束

create table student (

       sno number(4) primary key,

       sname varchar2(12),

       gender char(3),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50)

);

d)联合主键,只能在表级别定义,因为一个表只能有一个主键

create table student (

       sno number(4),

       sname varchar2(12),

       gender char(3),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50),

       constraints pk_student
primary key (sno, email)

);

3.2非空约束not null

字段值不允许为空,非空约束只能在列级别定义

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50),

       constraints pk_student
primary key (sno)

);

3.3唯一约束
(unique)

要求字段值不能重复;

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50), —
unique,

       constraints pk_student
primary key (sno),

       constraints uk_student_email
unique (email)

);

3.4
检查约束(check)

用于限定字段值的取值范围;

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3) check (gender
in (‘男’, ‘女’)),

       age number(3),

       sdate date,

       clazz varchar2(20),

       email varchar2(50),

       constraints pk_student
primary key (sno),

       constraints uk_student_email
unique (email),

       constraints ck_student_age check
(age between 18 and 30)

);

3.5外键约束(foreign
key)

用于约束表和表之间的关系,两张标的依赖关系,以emp表和dept表为例,emp表依赖dept表,因此,dept表可以被称之为主表,emp表被称之为从表。

注意:

》主表中,只有主键或唯一建才可以被从表参考;

》从表中作为外键的列,类型一定要和主表的被参考列相同;

a)建立主表clazz

create table clazz (

       cno number(3) primary key,

       cname varchar2(20) not null,

       croom number(3)

);

b)建立从表,并定义外键Student

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3) default ‘男’ check (gender in (‘男’, ‘女’)),

       age number(3),

       sdate date,

       email varchar2(50),

       cno number(3),

       constraints pk_student
primary key (sno),

       constraints uk_student_email
unique (email),

       constraints ck_student_age
check (age between 18 and 30),

       constraints fk_student_cno foreign
key (cno) references clazz (cno)

);

c)也可以在列级别定义外键,如下

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3) default ‘男’ check (gender in (‘男’, ‘女’)),

       age number(3),

       sdate date,

       email varchar2(50),

       cno number(3) references
clazz(cno),

       constraints pk_student
primary key (sno),

       constraints uk_student_email
unique (email),

       constraints ck_student_age
check (age between 18 and 30)

);

d)外键中的删除问题;

》主表中的数据在被引用时,无法直接删除,默认情况下,需要先删除主表中的数据。

》可以在定义外建时,设置删除策略为cascade(级联),表示当删除主表的信息时,同时删除从表中的所有关联的信息

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3) default ‘男’ check (gender in (‘男’, ‘女’)),

       age number(3),

       sdate date,

       email varchar2(50),

       cno number(3),

       constraints pk_student
primary key (sno),

       constraints uk_student_email
unique (email),

       constraints ck_student_age
check (age between 18 and 30),

       constraints fk_student_cno
foreign key (cno) references clazz (cno) on delete cascade

);

》可以在定义外建时,设置删除策略为set
null(设空),表示当删除主表的信息时,将从表中的相关数据设置为null

create table student (

       sno number(4),

       sname varchar2(12) not null,

       gender char(3) default ‘男’ check (gender in (‘男’, ‘女’)),

       age number(3),

       sdate date,

       email varchar2(50),

       cno number(3),

       constraints pk_student
primary key (sno),

       constraints uk_student_email
unique (email),

       constraints ck_student_age
check (age between 18 and 30),

       constraints fk_student_cno
foreign key (cno) references clazz (cno) on delete set null

);

3.6在修改表格时添加约束

a)给student表添加约束

— 主键约束

alter table student
add constraints pk_student primary key (sno);

— 唯一约束

alter table student
add constraints uk_student_email unique (email);

— 检查约束

alter table student
add constraints ck_student_age check (age between 18 and 30);

alter table student
add constraints ck_student_gender check (gender in (‘男’,’女’));

— 外键约束

alter table student
add constraints fk_student_cno foreign key (cno) references clazz
(cno);

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图