SQL Unique Value With Primary & Foreign Keys

Question

I'm pretty sure this question has been asked before but using SQL I'm trying to only allow a table to have a unique value using both a primary and foreign keys. This may sound confusing so these are my SQL statements.

Folder table:

CREATE TABLE [dbo].[Folder] 
(
    [FolderID] VARCHAR(50) NOT NULL,
    [FolderURL] NCHAR(255) NOT NULL,
    [FolderTag] VARCHAR(50) NOT NULL,

    PRIMARY KEY CLUSTERED ([FolderURL] ASC),
    UNIQUE NONCLUSTERED ([FolderID] ASC)
);

Extension table:

CREATE TABLE [dbo].[Extension] 
(
    [ExtensionID] VARCHAR(10) NOT NULL,
    [FolderID] VARCHAR(50) NOT NULL,
    [LocationURL] VARCHAR(MAX) NOT NULL,

    PRIMARY KEY CLUSTERED ([ExtensionID] ASC),

    CONSTRAINT [fk_FolderID] 
        FOREIGN KEY ([FolderID])
        REFERENCES [dbo].[Folder]([FolderID]) ON DELETE CASCADE,
    CONSTRAINT uc_Extension UNIQUE ([ExtensionID], [FolderID]) 
);

In the Extension table I would like to have it have a unique value using the [FolderID] & [ExtensionID]. I first tried to achieve this by creating a primary key using both values, then I tried the current statement above.

Edit

I didn't give enough detail for what I am asking, below is a layout of both tables that i want to create and what the results are using the methods above.

Folder Table:

----------------------------------------------------------
| Folder ID |        FolderURL        |  FolderTag       |
|-----------|-------------------------|------------------|
|   Home    | C:/users/test/          | Home Folder      |
| Downloads | E:/Donwloads            | Downloads Folder |
|   Music   | C:/users/test/music     | Music Folder     |
| Documents | C:/users/test/documents | Downloads Folder |
----------------------------------------------------------

Extension Table:

---------------------------------------------------------
| Extension ID | FolderID  |        LocationURL         |
|--------------|-----------|----------------------------|
|    .jpg      | Downloads | C:/users/test/pictures     | = Pass 
|    .png      | Downloads | C:/users/test/pictures     | = Pass
|    .docx     | Documents | C:/users/test/BusinessDocs | = Pass
|    .mp3      |   Home    | C:/users/test/music        | = Pass
|    .jpg      |   Home    | C:/users/test/pictures     | = Fail
|    .mp3      | Downloads | C:/users/test/music        | = Fail
---------------------------------------------------------

From the table layouts provided above you can see that the last two fail due to the .mp3 ID is already entered into the table. what I am asking about is the to create a table which has a unique primary key, or something else which will allow for a unique ExtensionID and FolderID so it will allow the last two values in the Extension Table.

If anyone could help me I would greatly appreciate it.

Why were you not able to make both into a primary key? That is acceptable. — Arin Taylor, Dec 27 '15 at 08:03
What is wrong with the current approach? With a UNIQUE constraint on `FolderID,ExtensionID`, you cannot store duplicates into that table...... so basically, this should do exactly what you want - doesn't it?? — marc_s, Dec 27 '15 at 08:18
I thought that i would be acceptable too but it seems that visual studio doesn't accept it, this could be because of VS, but i don't know. The statement i used for the using both as the primary key was:CREATE TABLE [dbo].[Extension] ( [ExtensionID] VARCHAR (10) NOT NULL, [FolderID] VARCHAR (50) NOT NULL, [LocationURL] VARCHAR (MAX) NOT NULL, PRIMARY KEY ([ExtensionID], [FolderID]), FOREIGN KEY ([FolderID]) REFERENCES [dbo].[Folder]([FolderID]) ); But it did seem to work — JoshuaJohnson2896, Dec 27 '15 at 08:25
You didn't answer my question: with this current approach, with the UNIQUE constraint - are you still able to insert **duplicate data** into your table?? You shouldn't be .... — marc_s, Dec 27 '15 at 08:31
You shouldn't be able to add duplicates, with this design. Have you considered switching your PKeys to the ID fields? It is unusual for FKeys to join to a non PKey. See this question for more: http://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key. — David Rushton, Dec 27 '15 at 10:31
Edit your question, and paste in SQL INSERT statments that demonstrate the problem. — Mike Sherrill 'Cat Recall', Dec 27 '15 at 15:56
I made some edits to the question and provided a layout of both tables to show what the current results are if that is any help. and to address marc_s response, it sad to that it will only allow an `ExtensionID` to be entered only once despite the `FolderID`. — JoshuaJohnson2896, Dec 27 '15 at 20:03

Mike Sherrill 'Cat Recall' · Accepted Answer · 2015-12-29T01:32:38.213

CREATE TABLE [dbo].[Extension] 
(
    [ExtensionID] VARCHAR(10) NOT NULL,
    [FolderID] VARCHAR(50) NOT NULL,
    [LocationURL] VARCHAR(MAX) NOT NULL,

    PRIMARY KEY ([ExtensionID], [FolderID]),

    CONSTRAINT [fk_FolderID] 
        FOREIGN KEY ([FolderID])
        REFERENCES [dbo].[Folder]([FolderID]) ON DELETE CASCADE
);

Here's full code and output. SQLFiddle.com is down for me; this is in PostgreSQL syntax.

CREATE TABLE Folder
(
    FolderID VARCHAR(50) NOT NULL,
    FolderURL NCHAR(255) NOT NULL,
    FolderTag VARCHAR(50) NOT NULL,

    PRIMARY KEY (FolderURL),
    UNIQUE (FolderID)
);

CREATE TABLE Extension 
(
    ExtensionID VARCHAR(10) NOT NULL,
    FolderID VARCHAR(50) NOT NULL,
    LocationURL VARCHAR(255) NOT NULL,

    PRIMARY KEY (ExtensionID, FolderID),

    CONSTRAINT fk_FolderID 
        FOREIGN KEY (FolderID)
        REFERENCES Folder(FolderID) ON DELETE CASCADE
);

insert into folder values
('Home', 'C:/users/test/', 'Home Folder'),
('Downloads', 'E:/Donwloads', 'Downloads Folder'),
('Music', 'C:/users/test/music', 'Music Folder'),
('Documents', 'C:/users/test/documents', 'Downloads Folder');

insert into Extension values
('.jpg', 'Downloads', 'C:/users/test/pictures'),
('.png', 'Downloads', 'C:/users/test/pictures'),
('.docx', 'Documents', 'C:/users/test/BusinessDocs'),
('.mp3', 'Home', 'C:/users/test/music'),
('.jpg', 'Home', 'C:/users/test/pictures'),
('.mp3', 'Downloads', 'C:/users/test/music');

select * from folder;

folderid    folderurl                 foldertag
--
Home        C:/users/test/            Home Folder
Downloads   E:/Donwloads              Downloads Folder
Music       C:/users/test/music       Music Folder
Documents   C:/users/test/documents   Downloads Folder

select * from extension;

extensionid  folderid    locationurl
--
.jpg         Downloads   C:/users/test/pictures
.png         Downloads   C:/users/test/pictures
.docx         Documents  C:/users/test/BusinessDocs
.mp3         Home        C:/users/test/music
.jpg         Home        C:/users/test/pictures
.mp3         Downloads   C:/users/test/music

Doesn't seem to work the way i would like it. I kinda wish i could explain what i would like better. — JoshuaJohnson2896, Dec 29 '15 at 01:13
*" Doesn't seem to work the way i would like it."* FWIW, that's pretty useless feedback. It works *exactly* like you *said* you wanted. See additional code and output. — Mike Sherrill 'Cat Recall', Dec 29 '15 at 01:34
Sorry for the lack of information and after wiping the entire DB and testing yours it works the way i need i too thanks. — JoshuaJohnson2896, Dec 30 '15 at 05:11

SQL Unique Value With Primary & Foreign Keys

1 Answers1