Multiple IDS and Constraint

Giganews Newsgroups
Subject: Multiple IDS and Constraint
Posted by:  Shapper (mdmou…
Date: Sun, 9 Dec 2012


I have a table Packs which "wraps" different formats of the same file:

create table dbo.Packs
  Id int identity not null
    constraint Packs_Id_PK primary key clustered (Id),
  Created datetime not null

create table dbo.Files
  Id int identity not null
    constraint Files_Id_PK primary key clustered (Id),
  PackId int not null,
  Data varbinary (max) filestream null
    constraint Files_Data_DF default (0x),    
  [Key] uniqueidentifier rowguidcol not null
    constraint Files_Key_U unique,      
  Mime nvarchar (200) not null
) filestream_on [STORAGE];

Consider a Pack ID = 1, for a video, with 3 files associated to it (File ID = 1, File ID = 2, File ID = 3).

What I would like is all files to have the same ID as the PACK ID = 1.

Then I would get a file using something like: GET FILE ID = PACK ID & FILE MIME = "video/mp4".

Is this possible?

And is it possible to set a constraint where for a given FILE ID there can be no repeated MIMES?

Thank You,