How to check and set a primary key with existing functions in sqlserver

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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

SQL

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.....

Posted 21-Feb-14 18:13pm

Naveen Kumar Malli

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Answer 1 · 2014-02-21T18:20:00

Solution 1

run this:

SQL

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'

Posted 21-Feb-14 18:20pm

King Fisher