SQL Foreign Keys/Relationships

Having briefly studied databases in college, I haven't worked with them since and have drawn a bit of a blank, so I was wondering if someone could help me out. I have a da开发者_JAVA百科tabase called Convert, which holds the following tables:

**File**
ID  int  PK
ISBN nvarchar(MAX)
UserName nvarchar(50)
CoverID
PDFID

**PDF**
PDFID int PK
FileContent image
MimeType nvarchar
FileName nvarchar

**Cover**
CoverID int PK
FileContent image
MimeType nvarchar
FileName nvarchar

I've just drawn a blank on two things really.

1. Relationships. I think if I a sql query such as below I will create foreign keys:

   Alter TABLE Cover ADD FOREIGN KEY (CoverID) REFERENCES File (CoverID)

What I need to do is create one to one relationships --> One File will have one Cover, and one PDF.

The second is thing I'm having difficulty getting my head around again is the insert statements. Any advice on how I should handle those would be appreciated?

I'm using SQL Server 2008 Also.

---

If you need to retain your current table structure (and @none is right - if it's really a one-to-one relationship there's no benefit to having three tables) you can get what you want by doing the following:

1. Define two foreign key constraints on File, one on File.PDFID referencing PDF.PDFID and the other on File.CoverID referencing Cover.CoverID.

2. Define two UNIQUE constraints on the File table, one on File.PDFID and the other on File.CoverID.

Share and enjoy.

---

if you want to ensure that a relation will have one to one relationship, then make one big table.

one table where you have

create table

ID  int  PK

ISBN nvarchar(MAX)

UserName nvarchar(50)


PDFFileContent image

PDFFileName nvarchar

CoverFileContent image

CoverFileName nvarchar

what you might ment in your original design is to make one table that could contain all 3 types and each row is different by having different value at "mime type" which is also possible, if hold keys that relet the table to itself.

such as

create table
ID int pk
ISBN nvarchar(max)
userName nvarchar(50)
pdfID int fk table2 id
coverID int fk table 2 id

create table2
id pk int
fileContent image
fileName nvarchar
mimetype (something)

---

A true one-to-one relationship would look like:

which is essentially a vertically partitioned table. In this case, you may also consider simply putting all columns in one table.