0

I have created a view in a SQL Server database which is just a join of two tables.

Is there any way I can insert a unique primary key into the rows of this view ...or I'm not sure how I can specify one of the column names to be a primary key...any ideas?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
thegunner
  • 6,883
  • 30
  • 94
  • 143
  • What kind of key? You can use `row_number()` to generate a unique number for each row. – Taryn Apr 05 '13 at 14:36
  • 1
    since it depends on the contents of the two joined tables, no not really. You can create unique identifiers ( concat two ids ) depending on the contents of those tables – Najzero Apr 05 '13 at 14:36
  • Could be any type...just an integer will do. – thegunner Apr 05 '13 at 14:38
  • where do I got to say this is the Primary Key? – thegunner Apr 05 '13 at 14:39
  • 1
    What requirements would you have for this primary key? Would it need to be *stable*? (I.e. if a row is deleted from one or other of the tables, do all of the primary key values need to remain as they have previously been observed?) What's your actual *goal* or *problem* here? – Damien_The_Unbeliever Apr 05 '13 at 14:42
  • I'm adding in a view to an entity framework model( haven't done this beofre) but I was getting some message abut needing a primary key.. – thegunner Apr 05 '13 at 14:45
  • It's always best to post the exact message: "some" message could be anything. And it's also helpful to state your actual problem, i.e. "I want to add a view to an EF model but I get the following error message: ...." With that additional information, it appears that your question may be a duplicate of [this one](http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view). – Pondlife Apr 07 '13 at 17:56

1 Answers1

5

You would have to create materialized (indexed) view in order to be able to add unique index. But you can't create PK constraint.

CREATE VIEW v_test
WITH SCHEMABINDING --optional
AS
    SELECT id from table

GO

CREATE UNIQUE CLUSTERED INDEX idx_id
    ON v_test (id)
GO
AdamL
  • 12,421
  • 5
  • 50
  • 74