?
Solved

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

Posted on 2008-10-22
9
Medium Priority
?
290 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

765 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