Technical Article

Design : Find table with Identities with missing unique constraints

,

This is to make you aware which tables have an autogenerated unique key but does not have any constraint forcing the inserted data to be unique.

Logging tables are the exception.

I have had occasions where duplicate Non-ID data was inserted into lookup tables and the same row gets described by 2 different id's in joins which cause difficult to resolve havoc in reporting, overnight processes and application logic.

Stops the below from happening, if it is still not understood what the intent is

create table destination(id int identity(1,1),name varchar(50),primary key clustered (ID))

GO

insert into Destination(Name)

values ('Blackheath')

GO 2

As always, talk to the devs, test test test before adding them to live.

select schema_name(o.schema_id)+'.'+o.name TableWithOnlyanIdentityPK,count(*) 
from sys.indexes  I
inner join sys.objects o on o.object_id = i.object_id and is_ms_shipped =0
inner join sys.identity_columns IC on ic.object_id = o.object_id
where is_unique =1 or is_primary_key=1
group by schema_name(o.schema_id)+'.'+o.name
having count(*) = 1

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1 (1)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1 (1)

You rated this post out of 5. Change rating

Related content

Technical Article

Data Degradation

The other day at a conference, the subject of data degradation/corrosion arose. The speaker at the conference said that data in a database degraded or corroded over time. The statement was made as if degradation over time applied to all databases. I found this blanket statement to be misguided. Indeed, I think that data does degrade in some databases, but not all.

2009-05-14

2,180 reads