Solved

How to set a primary key field when I select from several joins into a new table?

Posted on 2008-10-22
9
284 Views
Last Modified: 2010-03-20
Hi, I have the following selects.   How can I set the Id field to be a Primary key but not auto-increment?  Thank you.

SELECT a.Id
      ,a.MembershipLevel
      ,a.ProfessionalOrganizations
      ,a.Department
      ,a.Gender
      ,a.Ethnicity
      ,a.Race
      ,a.YearOfBirth
      ,a.Title
      ,a.EmailAddress
      ,a.LastName
      ,a.Firstname
      ,a.PhoneNumber
      ,a.Institution
      ,a.MailingAddress
      ,a.OfficeAddress
      ,a.TitleOther
      ,a.PhdTraineeCount
      ,a.MscTraineeCount
      ,a.InstitutionOther
      ,a.MembershipReasonOther
      ,a.RaceOther
      ,a.MailingAddressLine1
      ,a.MailingAddressLine2
      ,a.MailingAddressCity
      ,a.MailingAddressState
      ,a.MailingAddressZip
      ,a.CommunityOrganization
      ,C.[Degree 1]
      ,D.[Membership Reason 1]
      ,E.[Membership Service Requirement 1]
INTO Application
      FROM dbo.TempApplication2 a
            FULL JOIN dbo.Degree C on a.Id = C.ApplicationId
            FULL JOIN dbo.Reasons D on a.Id = D.ApplicationId
            FULL JOIN dbo.Requirement E on a.Id = E.ApplicationId
ORDER BY a.Id
0
Comment
Question by:lapucca
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
Try this:
ALTER TABLE Application ADD PRIMARY KEY(Id);

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
SQL doesn't allow explicit keys in SELECT statements or views. You can create a key on your table like this:

ALTER TABLE dbo.TempApplication2
  ADD CONSTRAINT TempApplication2_PK
  PRIMARY KEY (ID);
0
 

Expert Comment

by:Tejal06
Comment Utility
Actually your ID is in TempApplication2 table
therefore the query should be
ALTER TABLE TempApplication2 ADD PRIMARY KEY (ID); 

Open in new window

0
 

Author Comment

by:lapucca
Comment Utility
Hi Cxr,
Got the following error when I run it.  Please advise how to correct this.  Thank you.
Msg 8111, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table 'Application'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Expert Comment

by:Tejal06
Comment Utility
Your table Application has records where ID is NULL. You need to delete these records.

DELETE Application where ID IS NULL
0
 

Author Comment

by:lapucca
Comment Utility
I delete the null rows but still getting the same error message.  I think after deleting the Null rows I would need to set Id column not to accept null.  How to code this constraint?  thank you.
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
Comment Utility
A primary key column must be defined as "not null". Which table do you want this primary key on? And what datatype is the Id column. If the table is "Application" and the type is INT, use something like this to make the column "not null":

ALTER TABLE Application ALTER COLUMN Id INT NOT NULL;

Open in new window

0
 

Author Comment

by:lapucca
Comment Utility
Hi Cxr, Yes, that did it. Thank you.
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
Note that Cxr's ALTER TABLE statement adds a primary key with a default system-assigned name. This is not good practice in my opinion - in fact people who support SQL Server systems regularly have cause to curse people who do this! Always give your constraints explicit names. :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Using Case  in sql queries 17 58
Oracle SQL Select Statement 19 58
Setting a NULL value to an empty string in SQL 32 35
query question 13 40
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now