We help IT Professionals succeed at work.

MS SQL 2008

plambkin1
plambkin1 asked
on
Hi,

Look at the followinng table:-

CREATE TABLE [dbo].[PageItems](
      [pageId] [int] NOT NULL,
      [itemId] [int] NOT NULL
) ON [PRIMARY]
GO



ALTER TABLE [dbo].[PageItems]  WITH CHECK ADD  CONSTRAINT [FK_PageItems_Items] FOREIGN KEY([itemId])
REFERENCES [dbo].[Items] ([id])
GO
ALTER TABLE [dbo].[PageItems] CHECK CONSTRAINT [FK_PageItems_Items]
GO


ALTER TABLE [dbo].[PageItems]  WITH CHECK ADD  CONSTRAINT [FK_PageItems_Pages] FOREIGN KEY([pageId])
REFERENCES [dbo].[Pages] ([id])
GO
ALTER TABLE [dbo].[PageItems] CHECK CONSTRAINT [FK_PageItems_Pages]
GO


Basicly it creates a table with some columns.  It then assigns pageId and itemId as foreign keys.
These keys are actually primary keys in the tables [Pages] and [Items].

My question is : where is the primary key to the table [pageItems] assigned? It doesn't seem to be given a primary key?

Many thanks for any help.

Kind regards,

Paul
Comment
Watch Question

SQL Server DBA
Top Expert 2011
Commented:
There is no Primary key defined on table PageItems, to assign PK


ALTER TABLE [dbo].[PageITems] ADD CONSTRAINT [PK_PageItems] PRIMARY KEY CLUSTERED  ([Key Colums])

eg: set PageID as PK

ALTER TABLE [dbo].[PageITems] ADD CONSTRAINT [PK_PageItems] PRIMARY KEY CLUSTERED  ([pageId])
AnujSQL Server DBA
Top Expert 2011

Commented:
To List out the pk of table

use EXEC sp_pkeys  @table_name = 'PageItems'
     
Hi,
This is basically association table between tables [Pages] and [Items]. If you want want refer [PageItems] records to other tables then you can add primery key e.g. PageItemsId otherwise there is no need to add PK in this table.


Dani

Author

Commented:
Thanks Dani - this is not my code but somebody elses that I am trying to understand.

But if this is an association table between [Pages] and [Items] - how van there be more than one record
if there is no primary key.

Paul
Yah there is a chance for duplication of association if primary key not exist.
For example page2 is associated to item1 may repeat number of times.
But you can stop this duplication by adding composite primary key(by using pageid and itemid combined as primary key).


dani

Author

Commented:
Hi - so in essence the way the code is at the moment there will be only ever one record in the table PageItems.

Paul

Author

Commented:
Hi - by the way have you any idea why I am getting the error "Syntax error BOOLEAN" in the code below when I run it true Apache Derby

CREATE TABLE Answers (
      id INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
      text  VARCHAR(1000) NOT NULL,
      isCorrect BOOLEAN,
      explanation VARCHAR(1000) NULL,
      act BOOLEAN,
      version INTEGER NOT NULL
);

ALTER TABLE Answers ADD CONSTRAINT Answers_PRIMARY_KEY PRIMARY KEY (id);