如何在sqlserver中检查和设置主键与现有函数 [英] How to check and set a primary key with existing functions in sqlserver

问题描述

根据我的要求，我创建了一个名为Users的表，其中包含第一列的列（Userid，Usename，Password，Createddate，lastlogindate），即用户ID我没有应用任何约束......但是在创建表之后我需要为Userid列应用PRIMARY KEY我不需要改变表[Users] ..我做的是..我想检查表中的约束，如果我的列没有约束，查询必须设置约束..如果存在约束，则查询必须执行任何操作只需打印已存在的查询存在



我写的查询是

 如果 不 存在（选择 * 来自 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 其中 CONSTRAINT_TYPE = '  PRIMARY KEY'  AND  TABLE_NAME = ' 用户 和 TABLE_SCHEMA = '  dbo'）
 开始 
  alter   table 用户添加 约束 Pk_Userid 主要 密钥（Userid）
 结束 
  else  
 开始 
   -   密钥存在 
  end  

但是在'

 end 

'语句的查询结束时它发出错误

如何在正确的执行路径中犯错误.....

解决方案

运行此：

 如果 不 存在（选择 * 来自 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 其中 CONSTRAINT_TYPE = '  PRIMARY KEY'  AND  TABLE_NAME = ' 用户 和 TABLE_SCHEMA = '  dbo'）
  alter   table 用户 add  约束 Pk_Userid 主要 密钥（Userid）
  else  
  print  '  key exists' 

As per my requirement i had created a table of name Users with columns (Userid,Usename,Password,Createddate,lastlogindate) for the first column ie User id i haven't applied any constraint...but after creating table i need to apply PRIMARY KEY for the column Userid for that i need not want to alter the table [Users]..What the thing i did is ..i want to check the table for constraints, if there is no constraints on my column, the query must set the constraint.. and if there exists the constraints the query must do nothing just printing already query exists

the query which i had wrote is

if not Exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='Users' and TABLE_SCHEMA='dbo')
    begin
    alter table Users add constraint Pk_Userid Primary key(Userid)
    end
    else
    begin
       --key exists
    end

but at the end of query at the '

end

' statement it was throwing an error
How to make my mistake in a right path of execution.....

解决方案

run this:

if not Exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='Users' and TABLE_SCHEMA='dbo')
    alter table Users add constraint Pk_Userid Primary key(Userid)
    else
       print 'key exists'

这篇关于如何在sqlserver中检查和设置主键与现有函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！