0

In sql server I have the following table:

CREATE TABLE mydb.dbo.files (
    id bigint IDENTITY(1,1) NOT NULL,
    name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    folder bigint NULL,
    [type] nvarchar(10) NOT NULL CHECK ([type] IN('FILE','FOLDER')) DEFAULT 'FOLDER'
    CONSTRAINT PK__category__3213E83FDB19A582 PRIMARY KEY (id),
    CONSTRAINT folder_fk FOREIGN KEY (folder) REFERENCES mydb.dbo.files(id)
);

And I want the column folder to receive ids from files table that also have in the column type the value FOLDER. Can I do it with a constraint?

In order words I want the foreign key folder_fk to reference a file that has type FOLDER.

To be much more clear I try to do:

CREATE TABLE mydb.dbo.files (
    id bigint IDENTITY(1,1) NOT NULL,
    name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    folder bigint NULL,
    [type] nvarchar(10) NOT NULL CHECK ([type] IN('FILE','FOLDER')) DEFAULT 'FOLDER'
    CONSTRAINT PK__category__3213E83FDB19A582 PRIMARY KEY (id),
-- Idk whether syntax is correct I just want to showcase what I try to achieve
    CONSTRAINT folder_fk FOREIGN KEY (folder) REFERENCES mydb.dbo.files(id) HAVING `type` = "FOLDER"
);
4
  • Describe the system which you want to model. CONSTRAINT folder_fk FOREIGN KEY (folder) REFERENCES delta.dbo.files(id) Your FK constraint looks strange (a folder is a child of a file in another table).
    – Akina
    Commented Jan 10 at 12:56
  • What I want is to reference foreign key folder_fk to a record that has type FOLDER and I do not know how. Commented Jan 10 at 13:10
  • 1
    It seems the constraints use 3-part names to reference objects in different databases. Objects must be in the same database for referential constraints.
    – Dan Guzman
    Commented Jan 10 at 13:22
  • Updated the question. Commented Jan 10 at 13:56

1 Answer 1

0

Not directly.

You can't use a filtered unique index as the target for a foreign key, and unlike some DBs you can't have multiple NULLs in a non-filtered unique index, so you can't use an index on a computed column containing CASE WHEN type='FOLDER' THEN id ELSE NULL END.

You could use a negative value for non-folders and add an extra constraint on that, like so:

CREATE TABLE dbo.files (
    id bigint IDENTITY(1,1) NOT NULL,
    name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    folder bigint NULL CHECK (folder>-1),
    [type] nvarchar(10) NOT NULL CHECK ([type] IN('FILE','FOLDER')) DEFAULT 'FOLDER',
    folder_id AS CASE WHEN [type]='FOLDER' THEN id ELSE -id END PERSISTED UNIQUE,
    CONSTRAINT PK_files PRIMARY KEY CLUSTERED (id),
    CONSTRAINT folder_fk FOREIGN KEY (folder) REFERENCES dbo.files(folder_id)
);

This feels more than a little dirty due to the extra persisted computed column containing a mix of duplicated data (the folder id values) and essentially meaningless data (the negative values for files) but it achieves the constraints you want.


Side note: I also clustered your PK, or your table is a less efficient heap.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.