-1

I added a constraint primary key to my table and the query ran successfully. The dilemma I am facing is I cannot locate the primary key column in my table.

ALTER TABLE salesdata
ADD CONSTRAINT pk_salesdata PRIMARY KEY( "Address_of_New_Home","Sq_Ft","Build_Spec", "Realtor_Sale","Can","Actual_Sale_Date")

When I do:

select * from salesdata

It shows all the columns from before and no primary key column (pk_salesdata).

And even more baffling is when:

select pk_salesdata from salesdata

Database shows:

ERROR:  column "pk_salesdata" does not exist

I want to add primary key column to the table. I humbly request assistance of databasers.

  • 4
    ```pk_salesdata``` is the name of the primary key constraint, your primary key is a combination of all the columns inside the parenthesis. – Anand Jan 04 '17 at 21:31
  • Yes that is the name –  Jan 04 '17 at 21:32
  • 3
    That is not a column in the database you can select, it is the name of the constraint. – Anand Jan 04 '17 at 21:34
  • Is there anyway I can actually make this a column as well? –  Jan 04 '17 at 21:34
  • If you want a column that has the same value as your primary key you need to repeat your expression inside `SELECT` using [field concatenation](http://stackoverflow.com/questions/19942824/how-to-concatenate-columns-in-a-postgres-select). – PM 77-1 Jan 04 '17 at 21:35
  • 1
    You CANNOT make "this" into a column where "this" refers to the name of a constraint. You can add another column to the table and you can change the primary key from the current composite key to the newly added column (provided it is unique and non-null). – Anand Jan 04 '17 at 21:40
  • Can you please direct me on how to execute this? Yes this refers to the constraint pk_salesdata. –  Jan 04 '17 at 21:41
  • 1
    Making a column that is expected to duplicates one or more other columns is a bad idea. There is never a functional need to do so. It is occasionally useful to create such a denormalization for performance reasons, but it doesn't look like performance is a significant concern for you. – John Bollinger Jan 04 '17 at 21:42
  • 1
    It makes zero sense to take a bunch of distinct fields & combine them into a new column just because it's the key. What are you *actually* trying to accomplish here? – Sean McSomething Jan 04 '17 at 21:54
  • 1
    Maybe you should take a step back and describe _why_ you are trying to do this? What is the underlying problem you are trying to solve? –  Jan 04 '17 at 22:27
  • Horse, you have deep experience in databasing, is it better to make a primary key serial or primary key constraint? –  Jan 05 '17 at 14:50

3 Answers3

0

You create PRIMARY KEY, but...

  1. you create composed primary key from columns "Address_of_New_Home", "Sq_Ft", "Build_Spec", "Realtor_Sale", "Can", "Actual_Sale_Date" - it is not good idea

  2. your primary key have an alias name pk_salesdata, but it's only constraint name

  3. you didn't create new column

If you would like new synthetic primary key column you have to use command:

ALTER TABLE salesdata ADD COLUMN mynewautoincrementid SERIAL PRIMARY KEY;
Deadsheep39
  • 561
  • 3
  • 16
  • Thanks Deadsheep, why is it not a good idea? Address of New Home is redundant so needed to make a constraint which does not overshadow address. Adding the mynewautoincrementid will simply give each row an integer will not prevent duplicates from coming in. –  Jan 04 '17 at 22:31
  • Because with more indexed columns (in this case clustered index) more I/O per DML. In composed clustered index is in background created new invisible column which is unique and have only one column. Your qries cannot use indexes properly, you queries are slower. – Deadsheep39 Jan 05 '17 at 07:28
0

Primary Kay it's constraint in table when you add a primary key to any column you can select column name to show data

Sami Alhajj
  • 1
0

It looks like you've wrapped the columns you want to include in your primary keys in double quotes. That's not how this command works. Drop the quotes re-run the command and see what happens. 

ALTER TABLE salesdata
ADD CONSTRAINT pk_salesdata PRIMARY KEY( Address_of_New_Home,Sq_Ft,Build_Spec, Realtor_Sale,Can,Actual_Sale_Date)

It might be easier to add a primary index through the SQL GUI.

Here's the MS documentation page for creating a primary key.

https://msdn.microsoft.com/en-us/library/ms189039.aspx

Note: Since your adding a primary key don't expect it to be available as a column.

user3112728
  • 395
  • 1
  • 12