Link to home
Create AccountLog in
Avatar of Mezillinu
MezillinuFlag for Malta

asked on

Need to create a table to hold photos from a current table that I already have but I do not know how to create a foreign key in SQL

I have a table that currently supports 1 photo per product inserted.  I have created this script and will attach it below.  What I do not know how to do because I have never needed to do it is how to build a foreign key to another table which I need to create which will support multiple photos per item.

What I had in mind is to create a table, named tbl_pics which has a foreign unique key to ID.  Could somebody show me how to do it? It is a simple procedure but I have never done it before.

Thanks for your help, all help is appreciated!

GO
/****** Object:  Table [dbo].[tbl_products]    Script Date: 03/07/2008 23:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_products](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ImgType] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProductDescription] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[productPrice] [money] NULL,
	[ImageData] [image] NOT NULL,
 CONSTRAINT [PK_tbl_transaction_images] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mezillinu

ASKER

Could you please explain to me why are you taking this approach? I am used to using access - so Since I am new to SQL these things dont actually occour to me :S
good call...forgot to add that


CREATE TABLE tbl_ProductImages
(
ImageID INT IDENTITY(1,1) NOT NULL Primary Key Clustered,
ID INT NOT NULL REFERENCES tbl_products(ID),
[ImageData] [image] NOT NULL
It is standard database design....You use the 2nd table to store the pictures....it is a LOT easier to enter a record into a table rather than to add a field to a table each time a pic is added.  It saves space, and time.  

Here is a link on normalization (why its done).
http://en.wikipedia.org/wiki/Database_normalization
Ahhhhhh, you did not understand me I think!

I wanted another table to be created, with a first picture in tbl_products, and then the other pictures are in another table.

This is because what I had in mind, in my datagrid in ASP.NET, i make a navidational url, "View more pictures of this product" and then when i click that, i open a new window and do "select * from tbl_pics where picID=" & ID of selected row.

Does this make sense? Or do you think that I should put all images in one table?
I would put them in one table...but as I stated earlier, you can keep your image field in  your first table, and just add additional pictures to the new table.....
Ok! I will try this out! It is more than enough help, I am really greatful for all of your help!

Thanks alot have a nice day :-)
Which approach are you refering to?

If you are refering to the addition of an IDENTITY column.  Best practices dictate that you should always have a primary key.  Other than the occasional lookup table I use an IDENTITY (or GUID if using replication) in every table.  I have found through previous experience that the key value should be insignificant to the actual data.  For example if you were creating a table of people it would be reasonable to use something like an SSN as the key but if for some reason you ever had to change the SSN you would have to cascade that value through all referencing tables .  There are several other reasons that I won't go into such as join efficiency and the fact that it makes some queries much easier to write (remove duplicates) etal.
A hybrid of this if you want a primary photo but allow for multiples is to add a reference in the products table to the picture table.  This would allow you the many-to-one relationship but still allow you to designate a primary image for display purposes.  I've done this before with the primary address for a contact.

ALTER TABLE [dbo].[tbl_products]
ADD COLUMN PrimaryPic int NULL tbl_Pics(PicID)