sql server中创建数据库和表的语法5.7

下面是sql server中创建数据库，创建数据表以及添加约束的sql语句：

use master
--创建数据库
if exists (select * from sysdatabases where name = 'jobtest')
    drop database jobtest
create database jobtest
on
(
    name='jobtest_data',
    filename = 'D:\DB\jobtest_data.mdf',
    size = 10MB,
    filegrowth = 10%
)
log on 
(
    name = 'jobtest_log',
    filename = 'D:\DB\jobtest_log.ldf',
    size = 10MB,
    maxsize = 20MB,
    filegrowth = 1MB
)
go

--创建student表
if exists (select * from sysobjects where name = 'student')
drop table student 
create table student 
(
    id int not null,
    name varchar(50) not null,
    age int not null
)

go 

--创建grade表
if exists (select * from sysobjects where name = 'grade')
drop table grade
create table grade 
(
    id int not null,
    gname varchar(50) not null
)
--添加约束
--主键
alter table student 
add constraint pksid primary key (id) 
--唯一约束
alter table student 
add constraint ukid unique(id)
--默认约束
alter table student 
add constraint df_name default('张三') for name
--检查约束
alter table student 
add constraint ck_age check (age>0 and age<100) 

--必须给另一个表添加主键
alter table grade 
add constraint pkid primary key (id)

--外键约束
alter table student 
add constraint fkgid foreign key (id) references grade (id) 

        

          
        
        
        

          

          AI 代码解读