Setting PRIMARY KEY using Alter Table
-
Guys im having a problem with this statement.. ALTER TABLE tblSample ADD SampleField nvarchar (30) NOT NULL CONSTRAINT SampleField PRIMARY KEY im just trying to set my newly added column and at the same time setting it as a primary key.. but it wont work.. Is there any other way? or can it set an existing non primary column to a primary one.. if it can then how.. thanks..
-
Guys im having a problem with this statement.. ALTER TABLE tblSample ADD SampleField nvarchar (30) NOT NULL CONSTRAINT SampleField PRIMARY KEY im just trying to set my newly added column and at the same time setting it as a primary key.. but it wont work.. Is there any other way? or can it set an existing non primary column to a primary one.. if it can then how.. thanks..
Widgets wrote:
but it wont work..
Please supply error messages etc as "won't work" is an interpretation and not an observation of what actually happened. Have you checked that SampleField contains unique values? Primary keys can only be assigned to columns with unique values.
You always pass failure on the way to success.
-
Guys im having a problem with this statement.. ALTER TABLE tblSample ADD SampleField nvarchar (30) NOT NULL CONSTRAINT SampleField PRIMARY KEY im just trying to set my newly added column and at the same time setting it as a primary key.. but it wont work.. Is there any other way? or can it set an existing non primary column to a primary one.. if it can then how.. thanks..
Several problems: - You haven't specified a default value for SampleField and have disallowed NULLs so the database doesn't know what value to give that field for existing rows in the table (it would normally use NULL but you've disallowed it); - Even when you do add a default value or allow NULLs primary keys must be built on unique values, and every row in the table will then have the same value in that column; - The SQL syntax for ALTER TABLE doesn't allow you to do both at the same time anyway; - The SQL syntax for adding a primary key is:
ALTER TABLE tblSample
ADD CONSTRAINT PK_tblSample PRIMARY KEY
( SampleField )PK_tblSample is the name I've given to the constraint, which you need if you later want to drop it. If using CREATE TABLE, you can set the primary key to a single column simply by stating PRIMARY KEY after the column definition, but the name is generated by SQL Server making it harder to use later. You can define a primary key on a table that doesn't already have one by using the syntax above. You can create compound keys - keys consisting of multiple column values - by including all the key columns between the parentheses.
DoEvents: Generating unexpected recursion since 1991 -
Several problems: - You haven't specified a default value for SampleField and have disallowed NULLs so the database doesn't know what value to give that field for existing rows in the table (it would normally use NULL but you've disallowed it); - Even when you do add a default value or allow NULLs primary keys must be built on unique values, and every row in the table will then have the same value in that column; - The SQL syntax for ALTER TABLE doesn't allow you to do both at the same time anyway; - The SQL syntax for adding a primary key is:
ALTER TABLE tblSample
ADD CONSTRAINT PK_tblSample PRIMARY KEY
( SampleField )PK_tblSample is the name I've given to the constraint, which you need if you later want to drop it. If using CREATE TABLE, you can set the primary key to a single column simply by stating PRIMARY KEY after the column definition, but the name is generated by SQL Server making it harder to use later. You can define a primary key on a table that doesn't already have one by using the syntax above. You can create compound keys - keys consisting of multiple column values - by including all the key columns between the parentheses.
DoEvents: Generating unexpected recursion since 1991