Multiple IDS and Constraint

[Shapper]

Hello,

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,

Miguel

[rpresser]

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

You will need to either add a second column FileID to serve as the Files primary key ... or use a compound primary key (ID, Mime). Many textbooks would recommend the compound key. However, in large schemas, compound keys get obtrusive, and add lots of client side code. So often a single field primary key is used instead.

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

If you use a compound primary key, it's done already. If you don't, then you can add a unique constraint to do it:
ALTER COLUMN Files ADD CONSTRAINT Files_ID_Mime_UQ UNIQUE (ID, Mime)

[Shapper]

Post by rpresser

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

You will need to either add a second column FileID to serve as the Files primary key ... or use a compound primary key (ID, Mime). Many textbooks would recommend the compound key. However, in large schemas, compound keys get obtrusive, and add lots of client side code. So often a single field primary key is used instead.

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

ALTER COLUMN Files ADD CONSTRAINT Files_ID_Mime_UQ UNIQUE (ID, Mime)

Hello,

You mean something like:

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

create table dbo.Files
(
Id 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,
constraint IdMime_PK primary key clustered (Id, Mime)
) filestream_on [STORAGE];

alter table dbo.Files
add constraint Files_Id_FK foreign key (Id) references dbo.Packs(Id) on delete cascade on update cascade;

Is this what you mean?

Thank You,
Miguel

[rpresser]

Post by Shapper
Is this what you mean?

Yes, it is exactly what I meant.