dbo.unique_constraints (Unique keys)

Dataedo repository 7

- Dataedo repository 7
  - Modules
  - Tables

Documentation	Dataedo repository 7
Schema	dbo
Name	unique_constraints
Title	Unique keys
Module	Schema and Metadata
Type	Schema
Status	Active

Holds table unique keys, either defined in DBMS or by user in Dataedo. The two types are distinguished by source column (DBMS/USER).

Columns

		Name	Data type	Attributes	References	Description	Type	Status
	1	unique_constraint_id	int	Identity		Row id	Metadata	Active
Row id Identity Type: Metadata Status: Active
	2	table_id	int		dbo.tables (Tables and Views)	Table to which unique key belongs	Metadata	Active
Table to which unique key belongs References: dbo.tables (Tables and Views) Type: Metadata Status: Active
	3	name	nvarchar(250)			Unique constraint name from DBMS or assigned by user if constraint is user defined	Schema	Active
Unique constraint name from DBMS or assigned by user if constraint is user defined Type: Schema Status: Active
	4	source	nvarchar(50)	Default: 'USER'		Source of the row: 'DBMS' for imported from database, 'USER' for manual defined by user in Dataedo repository	Metadata	Active
Source of the row: 'DBMS' for imported from database, 'USER' for manual defined by user in Dataedo repository Default: 'USER' Type: Metadata Status: Active
	5	status	char(1)	Default: 'A'		'D' if unique key was deleted from DBMS, 'A' otherwise	Metadata	Active
'D' if unique key was deleted from DBMS, 'A' otherwise Default: 'A' Type: Metadata Status: Active
	6	primary_key	bit			1 when key is primary key, 0 when regular unique key	Schema	Active
1 when key is primary key, 0 when regular unique key Type: Schema Status: Active
	7	disabled	bit			1 when imported key is disabled (only DBMS)	Schema	Active
1 when imported key is disabled (only DBMS) Type: Schema Status: Active
	8	description	nvarchar(MAX)			Unique key description defined with Dataedo	Documentation	Active
Unique key description defined with Dataedo Type: Documentation Status: Active
	9	field1	nvarchar(MAX)			Custom field 1	Documentation	Active
Custom field 1 Type: Documentation Status: Active
	10	field2	nvarchar(MAX)			Custom field 2	Documentation	Active
Custom field 2 Type: Documentation Status: Active
	11	field3	nvarchar(MAX)			Custom field 3	Documentation	Active
Custom field 3 Type: Documentation Status: Active
	12	field4	nvarchar(MAX)			Custom field 4	Documentation	Active
Custom field 4 Type: Documentation Status: Active
	13	field5	nvarchar(MAX)			Custom field 5	Documentation	Active
Custom field 5 Type: Documentation Status: Active
	14	field6	nvarchar(MAX)			Custom field 6	Documentation	Active
Custom field 6 Type: Documentation Status: Active
	15	field7	nvarchar(MAX)			Custom field 7	Documentation	Active
Custom field 7 Type: Documentation Status: Active
	16	field8	nvarchar(MAX)			Custom field 8	Documentation	Active
Custom field 8 Type: Documentation Status: Active
	17	field9	nvarchar(MAX)			Custom field 9	Documentation	Active
Custom field 9 Type: Documentation Status: Active
	18	field10	nvarchar(MAX)			Custom field 10	Documentation	Active
Custom field 10 Type: Documentation Status: Active
	19	field11	nvarchar(MAX)			Custom field 11	Documentation	Active
Custom field 11 Type: Documentation Status: Active
	20	field12	nvarchar(MAX)			Custom field 12	Documentation	Active
Custom field 12 Type: Documentation Status: Active
	21	field13	nvarchar(MAX)			Custom field 13	Documentation	Active
Custom field 13 Type: Documentation Status: Active
	22	field14	nvarchar(MAX)			Custom field 14	Documentation	Active
Custom field 14 Type: Documentation Status: Active
	23	field15	nvarchar(MAX)			Custom field 15	Documentation	Active
Custom field 15 Type: Documentation Status: Active
	24	field16	nvarchar(MAX)			Custom field 16	Documentation	Active
Custom field 16 Type: Documentation Status: Active
	25	field17	nvarchar(MAX)			Custom field 17	Documentation	Active
Custom field 17 Type: Documentation Status: Active
	26	field18	nvarchar(MAX)			Custom field 18	Documentation	Active
Custom field 18 Type: Documentation Status: Active
	27	field19	nvarchar(MAX)			Custom field 19	Documentation	Active
Custom field 19 Type: Documentation Status: Active
	28	field20	nvarchar(MAX)			Custom field 20	Documentation	Active
Custom field 20 Type: Documentation Status: Active
	29	field21	nvarchar(MAX)			Custom field 21	Documentation	Active
Custom field 21 Type: Documentation Status: Active
	30	field22	nvarchar(MAX)			Custom field 22	Documentation	Active
Custom field 22 Type: Documentation Status: Active
	31	field23	nvarchar(MAX)			Custom field 23	Documentation	Active
Custom field 23 Type: Documentation Status: Active
	32	field24	nvarchar(MAX)			Custom field 24	Documentation	Active
Custom field 24 Type: Documentation Status: Active
	33	field25	nvarchar(MAX)			Custom field 25	Documentation	Active
Custom field 25 Type: Documentation Status: Active
	34	field26	nvarchar(MAX)			Custom field 26	Documentation	Active
Custom field 26 Type: Documentation Status: Active
	35	field27	nvarchar(MAX)			Custom field 27	Documentation	Active
Custom field 27 Type: Documentation Status: Active
	36	field28	nvarchar(MAX)			Custom field 28	Documentation	Active
Custom field 28 Type: Documentation Status: Active
	37	field29	nvarchar(MAX)			Custom field 29	Documentation	Active
Custom field 29 Type: Documentation Status: Active
	38	field30	nvarchar(MAX)			Custom field 30	Documentation	Active
Custom field 30 Type: Documentation Status: Active
	39	field31	nvarchar(MAX)			Custom field 31	Documentation	Active
Custom field 31 Type: Documentation Status: Active
	40	field32	nvarchar(MAX)			Custom field 32	Documentation	Active
Custom field 32 Type: Documentation Status: Active
	41	field33	nvarchar(MAX)			Custom field 33	Documentation	Active
Custom field 33 Type: Documentation Status: Active
	42	field34	nvarchar(MAX)			Custom field 34	Documentation	Active
Custom field 34 Type: Documentation Status: Active
	43	field35	nvarchar(MAX)			Custom field 35	Documentation	Active
Custom field 35 Type: Documentation Status: Active
	44	field36	nvarchar(MAX)			Custom field 36	Documentation	Active
Custom field 36 Type: Documentation Status: Active
	45	field37	nvarchar(MAX)			Custom field 37	Documentation	Active
Custom field 37 Type: Documentation Status: Active
	46	field38	nvarchar(MAX)			Custom field 38	Documentation	Active
Custom field 38 Type: Documentation Status: Active
	47	field39	nvarchar(MAX)			Custom field 39	Documentation	Active
Custom field 39 Type: Documentation Status: Active
	48	field40	nvarchar(MAX)			Custom field 40	Documentation	Active
Custom field 40 Type: Documentation Status: Active
	49	creation_date	datetime	Default: getdate()		Standard row creation timestamp	Metadata	Active
Standard row creation timestamp Default: getdate() Type: Metadata Status: Active
	50	created_by	nvarchar(1024)	Default: user_name()		Dataedo username that created row	Metadata	Active
Dataedo username that created row Default: user_name() Type: Metadata Status: Active
	51	last_modification_date	datetime	Default: getdate()		Standard row last update timestamp	Metadata	Active
Standard row last update timestamp Default: getdate() Type: Metadata Status: Active
	52	modified_by	nvarchar(1024)	Default: user_name()		Dataedo username that last updated row	Metadata	Active
Dataedo username that last updated row Default: user_name() Type: Metadata Status: Active
	53	source_id	int			For internal use only	Internal	Active
For internal use only Type: Internal Status: Active
	54	temp_sync_status	bit	Default: 0		For internal use only	Internal	Active
For internal use only Default: 0 Type: Internal Status: Active
	55	update_id	int		dbo.schema_updates (Imports/Updates)	Id of the last schema import/update from source database where element was created/modified	Metadata	Active
Id of the last schema import/update from source database where element was created/modified References: dbo.schema_updates (Imports/Updates) Type: Metadata Status: Active

Relations

Foreign table	Primary table	Join	Title / Name / Description
dbo.unique_constraints (Unique keys)	dbo.schema_updates (Imports/Updates)	dbo.unique_constraints.update_id = dbo.schema_updates.update_id	fk_unique_constraints_schema_updates
dbo.unique_constraints.update_id = dbo.schema_updates.update_id Name: fk_unique_constraints_schema_updates
dbo.unique_constraints (Unique keys)	dbo.tables (Tables and Views)	dbo.unique_constraints.table_id = dbo.tables.table_id	FK_unique_constraints_tables
dbo.unique_constraints.table_id = dbo.tables.table_id Name: FK_unique_constraints_tables
dbo.unique_constraints_changes	dbo.unique_constraints (Unique keys)	dbo.unique_constraints_changes.unique_constraint_id = dbo.unique_constraints.unique_constraint_id	fk_unique_constraints_changes_unique_constraints
dbo.unique_constraints_changes.unique_constraint_id = dbo.unique_constraints.unique_constraint_id Name: fk_unique_constraints_changes_unique_constraints
dbo.unique_constraints_columns (Unique keys' columns)	dbo.unique_constraints (Unique keys)	dbo.unique_constraints_columns.unique_constraint_id = dbo.unique_constraints.unique_constraint_id	FK_unique_constraints_columns_unique_constraints
dbo.unique_constraints_columns.unique_constraint_id = dbo.unique_constraints.unique_constraint_id Name: FK_unique_constraints_columns_unique_constraints
dbo.unique_constraints_columns_changes	dbo.unique_constraints (Unique keys)	dbo.unique_constraints_columns_changes.unique_constraint_id = dbo.unique_constraints.unique_constraint_id	fk_unique_constraints_columns_changes_unique_constraints
dbo.unique_constraints_columns_changes.unique_constraint_id = dbo.unique_constraints.unique_constraint_id Name: fk_unique_constraints_columns_changes_unique_constraints

Unique keys

	Key name	Columns	Description
	PK_unique_constraints	unique_constraint_id	Table primary key
Table primary key

Triggers

	Key name	When	Description
	trg_unique_constraints_change_track_insert	After Insert	Schema change tracking trigger
-- ============================================= -- Description: Insert constraint's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_unique_constraints_change_track_insert] ON [unique_constraints] FOR INSERT AS -- skip manual objects created through Dataedo application -- or first documentation import to Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] WHERE [source] = 'USER' ) OR EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] JOIN [schema_updates] [u] ON [u].[update_id] = [i].[update_id] AND [u].[type] = 'IMPORT' ) BEGIN RETURN; END; INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[unique_constraint_id], [i].[table_id], [i].[name], [i].[primary_key], [i].[disabled], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] JOIN [tables] [t] ON [t].[table_id] = [i].[table_id];
Schema change tracking trigger
-- ============================================= -- Description: Insert constraint's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_unique_constraints_change_track_insert] ON [unique_constraints] FOR INSERT AS -- skip manual objects created through Dataedo application -- or first documentation import to Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] WHERE [source] = 'USER' ) OR EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] JOIN [schema_updates] [u] ON [u].[update_id] = [i].[update_id] AND [u].[type] = 'IMPORT' ) BEGIN RETURN; END; INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[unique_constraint_id], [i].[table_id], [i].[name], [i].[primary_key], [i].[disabled], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] JOIN [tables] [t] ON [t].[table_id] = [i].[table_id];
	trg_unique_constraints_change_track_update	After Update	Schema change tracking trigger
-- ============================================= -- Description: Insert constraint's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_unique_constraints_change_track_update] ON [unique_constraints] FOR UPDATE AS -- skip manual objects created through Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [INSERTED] WHERE [source] = 'USER' ) BEGIN RETURN; END; -- check if object property change IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] WHERE ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[primary_key],'') <> [i].[primary_key] OR ISNULL([d].[disabled],'') <> [i].[disabled] ) BEGIN UPDATE [unique_constraints_changes] SET [valid_to] = GETDATE() FROM [unique_constraints_changes] [c] INNER JOIN [inserted] [u] ON [c].[unique_constraint_id] = [u].[unique_constraint_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for constraint - when constraint was updated INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [before_name], [before_primary_key], [before_disabled], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[unique_constraint_id], [i].[table_id], [i].[name], [i].[primary_key], [i].[disabled], [d].[name], [d].[primary_key], [d].[disabled], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] JOIN [tables] [t] ON [t].[table_id] = [d].[table_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [unique_constraints_changes] [c] WHERE [c].[unique_constraint_id] = [d].[unique_constraint_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[primary_key],'') <> [i].[primary_key] OR ISNULL([d].[disabled],'') <> [i].[disabled]); END; BEGIN -- insert changes (deleted values) for constraint - when constraint was deleted INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [operation], [valid_to], [update_id] ) SELECT [t].[database_id], [d].[unique_constraint_id], [d].[table_id], [d].[name], [d].[primary_key], [d].[disabled], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] JOIN [tables] [t] ON [t].[table_id] = [d].[table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for constraint - when constraint was restored INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[unique_constraint_id], [i].[table_id], [i].[name], [i].[primary_key], [i].[disabled], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] JOIN [tables] [t] ON [t].[table_id] = [d].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
Schema change tracking trigger
-- ============================================= -- Description: Insert constraint's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_unique_constraints_change_track_update] ON [unique_constraints] FOR UPDATE AS -- skip manual objects created through Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [INSERTED] WHERE [source] = 'USER' ) BEGIN RETURN; END; -- check if object property change IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] WHERE ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[primary_key],'') <> [i].[primary_key] OR ISNULL([d].[disabled],'') <> [i].[disabled] ) BEGIN UPDATE [unique_constraints_changes] SET [valid_to] = GETDATE() FROM [unique_constraints_changes] [c] INNER JOIN [inserted] [u] ON [c].[unique_constraint_id] = [u].[unique_constraint_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for constraint - when constraint was updated INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [before_name], [before_primary_key], [before_disabled], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[unique_constraint_id], [i].[table_id], [i].[name], [i].[primary_key], [i].[disabled], [d].[name], [d].[primary_key], [d].[disabled], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] JOIN [tables] [t] ON [t].[table_id] = [d].[table_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [unique_constraints_changes] [c] WHERE [c].[unique_constraint_id] = [d].[unique_constraint_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[primary_key],'') <> [i].[primary_key] OR ISNULL([d].[disabled],'') <> [i].[disabled]); END; BEGIN -- insert changes (deleted values) for constraint - when constraint was deleted INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [operation], [valid_to], [update_id] ) SELECT [t].[database_id], [d].[unique_constraint_id], [d].[table_id], [d].[name], [d].[primary_key], [d].[disabled], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] JOIN [tables] [t] ON [t].[table_id] = [d].[table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for constraint - when constraint was restored INSERT INTO [unique_constraints_changes] ([database_id], [unique_constraint_id], [table_id], [name], [primary_key], [disabled], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[unique_constraint_id], [i].[table_id], [i].[name], [i].[primary_key], [i].[disabled], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[unique_constraint_id] = [d].[unique_constraint_id] JOIN [tables] [t] ON [t].[table_id] = [d].[table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
	trg_unique_constraints_Modify	After Insert, Update	Standard insert/update trigger setting last_modification_date, modified by columns
-- ============================================= -- Author: Piotr Kononow -- Create date: 2014-06-18 -- Description: Updates last_modification_date, modified_by columns on insert or update -- ============================================= create TRIGGER [trg_unique_constraints_Modify] ON [unique_constraints] AFTER INSERT, UPDATE AS BEGIN UPDATE [unique_constraints] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE unique_constraint_id IN (SELECT DISTINCT unique_constraint_id FROM Inserted) END
Standard insert/update trigger setting last_modification_date, modified by columns
-- ============================================= -- Author: Piotr Kononow -- Create date: 2014-06-18 -- Description: Updates last_modification_date, modified_by columns on insert or update -- ============================================= create TRIGGER [trg_unique_constraints_Modify] ON [unique_constraints] AFTER INSERT, UPDATE AS BEGIN UPDATE [unique_constraints] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE unique_constraint_id IN (SELECT DISTINCT unique_constraint_id FROM Inserted) END

Uses

Name
dbo.unique_constraints (Unique keys)
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views)
dbo.trg_unique_constraints_change_track_insert
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views)
dbo.unique_constraints_changes
dbo.trg_unique_constraints_change_track_update
dbo.tables (Tables and Views)
dbo.unique_constraints_changes
dbo.trg_unique_constraints_Modify
dbo.unique_constraints (Unique keys)

Used by

Name
dbo.unique_constraints (Unique keys)
dbo.trg_unique_constraints_cols_change_track_insert
dbo.trg_unique_constraints_cols_change_track_update
dbo.trg_unique_constraints_Modify
dbo.unique_constraints_changes
dbo.unique_constraints_columns (Unique keys' columns)
dbo.unique_constraints_columns_changes

Exported: 2018-12-06 15:15, Last imported: 2018-11-21 15:11