June 2, 2011 at 3:39 pm
Hi there.
Could someone tell me whether auto-generated key names in DB deterministic? I'm afraid of they're not and this is maybe an issue if I wrote a change script for such a key to run on different PCs?
Thanks
June 2, 2011 at 4:36 pm
Not sure how they're generated internally but they look random. It is a better practice to explicitly name all your keys, constraints, indexes, defaults, etc. instead of allowing the database engine to name them.
To name your key in a create table statement:
SET NOCOUNT ON ;
GO
USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.table_name')
AND type IN (N'U') )
DROP TABLE dbo.table_name ;
GO
CREATE TABLE dbo.table_name
(
id INT NOT NULL
IDENTITY(1, 1)
PRIMARY KEY,
name VARCHAR(100) NOT NULL
DEFAULT ('My Name')
) ;
GO
SELECT i.name
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
WHERE t.name = 'table_name'
UNION
SELECT dc.name
FROM sys.tables t
JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
WHERE t.name = 'table_name' ;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.table_name')
AND type IN (N'U') )
DROP TABLE dbo.table_name ;
GO
CREATE TABLE dbo.table_name
(
id INT NOT NULL
IDENTITY(1, 1),
name VARCHAR(100) NOT NULL
CONSTRAINT [df_dbo.table_name.name] DEFAULT ('My Name'),
CONSTRAINT [pk_dbo.table_name.id] PRIMARY KEY (id)
) ;
GO
SELECT i.name
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
WHERE t.name = 'table_name'
UNION
SELECT dc.name
FROM sys.tables t
JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
WHERE t.name = 'table_name' ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply