Solved

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

Posted on 2008-10-22
9
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 39

Expert Comment

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

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 22781150
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
ID: 22781180
Actually your ID is in TempApplication2 table
therefore the query should be
ALTER TABLE TempApplication2 ADD PRIMARY KEY (ID); 

Open in new window

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:lapucca
ID: 22781195
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
 

Expert Comment

by:Tejal06
ID: 22781230
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
ID: 22781263
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
ID: 22781285
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
ID: 22781351
Hi Cxr, Yes, that did it. Thank you.
0
 
LVL 22

Expert Comment

by:dportas
ID: 22781374
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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