Sql-server – Verify conditions before creating a constraint

constraintsql server

I've taken over an application database that has been grown from an Access db. As such, the database is in need of some significant refactoring.

The first step that I'm planning is adding a number of Foreign (and at least one Primary) Key constraints. One-by-one, I've been tracking down constraints that have failed when I try to apply them to my development copy, but I plan on adding a significant number of check constraints in the future.

I'd like to keep a running report of problem areas I need to focus on before I can apply any structural changes. Is there a way to verify the constraint criteria on the live system without attempting any schema or other changes?

Best Answer

You can find duplicate PKs:

select pk_col, count(*)
from tab
group by pk_col
having count(*) > 1 -- multiple rows with the same PK exist

Or the actual rows violating the PK:

select * from tab
where pk_col in 
 (
   select pk_col
   from tab
   group by pk_col
   having count(*) > 1 -- multiple rows with the same PK exist
 )

This returns bad FKs:

select * from child_tab as c
where not exists  -- invalid values in FK column
 (
   select * from parent_tab as p
   where c.fk_col = p.pk_col
 )

Now you can fix the bad data before you add the constraint.

Related Solutions

SQL Server – When to Run DBCC CLEANTABLE to Reclaim Space

The solution I'd think of for this problem is to run weekly a job that will run sp_spaceused for all tables in a database and save this data in a table. If there are differences in size for each table greater than..let's say..10%, I would run the dbcc cleantable.

My code to loop through table sizes looks like that:

if OBJECT_ID ('tempdb.dbo.#temp') is not null
    drop table #temp;

if OBJECT_ID ('dbo.BigTables') is not null
    drop table dbo.BigTables;
go

CREATE TABLE [dbo].[BigTables] (
    [table_name] [sysname] NOT NULL,
    [row_count] [int] NULL,
    [col_count] [int] NULL,
    [data_size] [varchar](50) NULL,
    [Date] [datetime] NOT NULL,
    [DBName] [nvarchar](128) NULL
);
GO

CREATE TABLE #temp (
    table_name sysname ,
    row_count int,
    reserved_size varchar(50),
    data_size varchar(50),
    index_size varchar(50),
    unused_size varchar(50)
);
go

INSERT     #temp
EXEC       sp_msforeachtable 'sp_spaceused ''?'''

insert into dbo.BigTables
SELECT     a.table_name,
           a.row_count,
           count(*) as col_count,
           a.data_size,
           getdate() as [Date],
    'MY DB' as DBName
FROM       #temp a
INNER JOIN information_schema.columns b
           ON a.table_name collate database_default
                = b.table_name collate database_default
GROUP BY   a.table_name, a.row_count, a.data_size
ORDER BY   CAST(Replace(a.data_size, ' KB', '') as integer) desc

DROP TABLE #temp;

Select * from dbo.BigTables;

Now you only need to build the logic that will verify what would be the size change through the week and schedule it.

Sql-server – good way to link tables and sequences

Easy in Oracle:

comment on table mytable is 'This is a table comment';

comment on column mytable.mycolumn is 'This is a column comment';

You can then view the comments using the USER_TAB_COMMENTS and USER_COL_COMMENTS data dictionary views.

This answer covers it for SQL Server.

Best Answer

Related Solutions

SQL Server – When to Run DBCC CLEANTABLE to Reclaim Space

Sql-server – good way to link tables and sequences

Related Question