October 19, 2015 at 12:15 pm
How to add a primary key for existing column in the table
Syntax ----- Help please
October 19, 2015 at 12:18 pm
mohant2305 (10/19/2015)
How to add a primary key for existing column in the tableSyntax ----- Help please
Start here: https://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx.
October 20, 2015 at 8:05 am
use below code to create primary key on existing table.
ALTER TABLE <table name>
ADD PRIMARY KEY (<column name>)
but the column you are going to specify in syntax should not have duplicate and null values.
October 21, 2015 at 4:05 am
Also verify the current collation, if column is nvarchar.
But first verify duplicates
-- List duplicates for column
select A.<column_name>
from <table_name> as A
left outer join <table_name> as B
on A.<column_name> = B.<column_name>
where A.<column_name> <> B.<column_name>
order BY A.<column_name>;
October 21, 2015 at 5:21 am
jonas.gunnarsson 52434 (10/21/2015)
Also verify the current collation, if column is nvarchar.But first verify duplicates
-- List duplicates for column
select A.<column_name>
from <table_name> as A
left outer join <table_name> as B
on A.<column_name> = B.<column_name>
where A.<column_name> <> B.<column_name>
order BY A.<column_name>;
Verify duplicates? How does that work?
;WITH table_name (column_name) AS (SELECT * FROM (VALUES ('A'),('A'),('B'),('C'),('D'),('D'),('E')) d (Letter))
-- List duplicates for column
SELECT A.column_name
FROM table_name as A
LEFT OUTER JOIN table_name as B
ON A.column_name = B.column_name
WHERE A.column_name <> B.column_name
ORDER BY A.column_name;
Verify the current collation of what? For what purpose?
What is the significance of nvarchar in this context?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 21, 2015 at 7:56 am
jonas.gunnarsson 52434 (10/21/2015)
Also verify the current collation, if column is nvarchar.But first verify duplicates
-- List duplicates for column
select A.<column_name>
from <table_name> as A
left outer join <table_name> as B
on A.<column_name> = B.<column_name>
where A.<column_name> <> B.<column_name>
order BY A.<column_name>;
There are several problems with this query.
1) The WHERE clause converts your OUTER JOIN to an INNER JOIN.
2) The ON clause and the WHERE clause contradict each other. Two values cannot simultaneously be equal and not equal to each other.
3) The primary key creation will fail if there are two records or more records with NULL values, but your query filters out NULL values.
4) A self-join is not the most efficient way to check for duplicate records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 21, 2015 at 9:34 am
Determine if ColumnA has duplicate values:
select
ColumnA,
count(*)
from
dbo.MyTable
group by
ColumnA
having
count(*) > 1;
Hopefully I got the order of the clauses correct.
October 21, 2015 at 9:54 am
Lynn Pettis (10/21/2015)
Determine if ColumnA has duplicate values:
select
ColumnA,
count(*)
from
dbo.MyTable
group by
ColumnA
having
count(*) > 1;
Hopefully I got the order of the clauses correct.
Looks correct to me.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 22, 2015 at 12:06 am
Lynn Pettis (10/21/2015)
Determine if ColumnA has duplicate values:
select
ColumnA,
count(*)
from
dbo.MyTable
group by
ColumnA
having
count(*) > 1;
Works like a charm, thanks 😉
? I think it could be problem to change collation after you set the primary key ?
(In some languages, any way it could be duplicates)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply