Constraint for Only One Record Marked as Default

Constraint for only one record marked as default

Here's a modification of Damien_The_Unbeliever's solution that allows one default per FormID.

CREATE VIEW form_defaults
AS
SELECT FormID
FROM whatever
WHERE isDefault = 1
GO
CREATE UNIQUE CLUSTERED INDEX ix_form_defaults on form_defaults (FormID)
GO

But the serious relational folks will tell you this information should just be in another table.

CREATE TABLE form
FormID int NOT NULL PRIMARY KEY
DefaultWhateverID int FOREIGN KEY REFERENCES Whatever(ID)

Ensure that one and only one default is defined for a set

How about changing the schema to

CREATE TABLE Customer
(
Id INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)

CREATE TABLE [Address]
(
Id INT PRIMARY KEY,
Address VARCHAR(500) NOT NULL
)


CREATE TABLE CustDefaultAddress
(
CustomerId INT PRIMARY KEY, /*Ensures no more than one default*/
AddressId INT,
FOREIGN KEY (CustomerId) REFERENCES Customer(Id),
FOREIGN KEY (AddressId) REFERENCES [Address](Id)
)


CREATE TABLE CustSecondaryAddress
(
CustomerId INT REFERENCES CustDefaultAddress(CustomerId),
/*No secondary address can be added unless default one exists*/
AddressId INT REFERENCES [Address](Id),
PRIMARY KEY(CustomerId, AddressId)
)

If there is an additional requirement that an address must not be present as both a primary and secondary address you can enforce this with a helper table and an indexed view.

CREATE TABLE dbo.TwoRows
(
X INT PRIMARY KEY
);

INSERT INTO dbo.TwoRows
VALUES (1),
(2)

GO

CREATE VIEW V
WITH SCHEMABINDING
AS
SELECT D.AddressId,
D.CustomerId
FROM dbo.CustDefaultAddress D
JOIN dbo.CustSecondaryAddress S
ON D.AddressId = S.AddressId
AND D.CustomerId = S.CustomerId
CROSS JOIN dbo.TwoRows

GO

CREATE UNIQUE CLUSTERED INDEX IX
ON V(AddressId, CustomerId)

Check constraint to allow only one combination of two columns

Use a filtered index, something like this:

CREATE TABLE PhoneNumber (
ID int primary key,
PhoneNumber nvarchar(20),
PersonID int,
IsDefault bit
)
GO

CREATE UNIQUE INDEX UX_Default_PhoneNumber
ON PhoneNumber(PersonID)
WHERE IsDefault = 1
GO

This enforces a unique PersonID only where IsDefault is true.

Mark One and Only One Record as Primary

Your second method is basically the right way. But, you want to be sure that the name is for the person. So:

create table person (
id int unsigned not null primary key,
primary_name_id int unsigned default null,
foreign key (id, primary_name_id) references name (person_id, id)
);

create table name (
id int unsigned not null primary key,
person_id int unsigned not null,
first_name varchar(191),
last_name varchar(191),
foreign key (person_id) references person (id),
unique (person_id, id);
);

The unique constraint is a bit of redundancy, but it let's you ensure that the values match across the two tables -- appropriately.

Is it possible to restrict a sql table to only have a single row at the design stage

The obvious method uses a trigger on insert to be sure the table is empty.

I've never tried this, but an index on a computed column might also work:

alter table dbo.KeyNumbers add OneAndOnly as ('OneAndOnly');

alter table dbo.KeyNumbers add constraint unq_OneAndOnly unique (OneAndOnly);

This should generate a unique key violation if a second row is inserted.

What to do when I want to use database constraints but only mark as deleted instead of deleting?

You could add the id value to the end of the name when a record is deleted, so when someone deletes id 3 the name becomes Thingy3_3 and then when they delete id 100 the name becomes Thingy3_100. This would allow you to create a unique composite index on the name and deleted fields but you then have to filter the name column whenever you display it and remove the id from the end of the name.

Perhaps a better solution would be to replace your deleted column with a deleted_at column of type DATETIME. You could then maintain a unique index on name and deleted at, with a non-deleted record having a null value in the deleted_at field. This would prevent the creation of multiple names in an active state but would allow you to delete the same name multiple times.

You obviously need to do a test when undeleting a record to ensure that there is no row with the same name and a null deleted_at field before allowing the un-delete.

You could actually implement all of this logic within the database by using an INSTEAD-OF trigger for the delete. This trigger would not delete records but would instead update the deleted_at column when you deleted a record.

The following example code demonstrates this

CREATE TABLE swtest (  
id INT IDENTITY,
name NVARCHAR(20),
deleted_at DATETIME
)
GO
CREATE TRIGGER tr_swtest_delete ON swtest
INSTEAD OF DELETE
AS
BEGIN
UPDATE swtest SET deleted_at = getDate()
WHERE id IN (SELECT deleted.id FROM deleted)
AND deleted_at IS NULL -- Required to prevent duplicates when deleting already deleted records
END
GO

CREATE UNIQUE INDEX ix_swtest1 ON swtest(name, deleted_at)

INSERT INTO swtest (name) VALUES ('Thingy1')
INSERT INTO swtest (name) VALUES ('Thingy2')
DELETE FROM swtest WHERE id = SCOPE_IDENTITY()
INSERT INTO swtest (name) VALUES ('Thingy2')
DELETE FROM swtest WHERE id = SCOPE_IDENTITY()
INSERT INTO swtest (name) VALUES ('Thingy2')

SELECT * FROM swtest
DROP TABLE swtest

The select from this query returns the following


id name deleted_at
1 Thingy1 NULL
2 Thingy2 2009-04-21 08:55:38.180
3 Thingy2 2009-04-21 08:55:38.307
4 Thingy2 NULL

So within your code you can delete records using a normal delete and let the trigger take care of the details. The only possible issue (That I could see) was that deleting already deleted records could result in duplicate rows, hence the condition in the trigger to not update the deleted_at field on an already deleted row.



Related Topics



Leave a reply



Submit