SQL Server confused about primary keys. So am I.
From: John Mullin (john.mullin_at_gmail.com)
Date: 01/06/05
- Next message: Dejan Sarka: "Re: sql server alerts"
- Previous message: lwood: "Re: SQL Backup and Restore"
- Messages sorted by: [ date ] [ thread ]
Date: 5 Jan 2005 23:11:00 -0800
We are generating a script to convert a client's database to Sql
Server. As part of this process, we are adding identity and timestamp
columns to each table, updating foreign keys to reflect the new
structure etc.
When we access the new database schema via OleDb, several (not all)
tables appear to be missing primary keys. Part of the code we use is
listed below:
////////////////////////////////////////////////////////////////////
/// <summary>
/// Load the table's primary key details.
/// </summary>
////////////////////////////////////////////////////////////////////
public bool LoadPrimaryKeys (OleDbConnection con)
{
// Get the primary keys.
DataTable primaryKeys =
con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
new Object[] {null, this.Schema.DataServices.Schema, _name});
// Ensure that that there is only one field in the key.
if (primaryKeys.Rows.Count != 1)
{
Reporter.WriteLine("Table " + _name +
" does not have a single field primary key");
}
bool first = true;
...
However, if we look at the tables in Enterprise Manager, the keys are
definitely there. Additionally:
a) Generating a database script from Enterprise Manager, and then
re-importing to a new database fixes the problem.
b) Detailed comparison of the databases using 2 commercial tools
(by Apex and Red Gate) shows the original and re-imported
databases to be identical.
DBCC (various options chosen) doesn't either reveal or fix the
problem.
I have included a script which replicates the problem, for a small
subset of Northwind.
Thanks
John
/*****************************************************************************
* Create the demo database.
*****************************************************************************/
/*
* Create tables.
*/
CREATE TABLE dbo.Categories
(
CategoryID int NOT NULL,
CategoryName nvarchar(15) NOT NULL,
Description ntext NULL,
Picture binary NULL
)
CREATE TABLE dbo.Products
(
CategoryID int NULL,
Discontinued bit NOT NULL,
ProductID int NOT NULL,
ProductName nvarchar(40) NOT NULL,
QuantityPerUnit nvarchar(20) NULL,
ReorderLevel smallint NULL,
SupplierID int NULL,
UnitPrice money NULL,
UnitsInStock smallint NULL,
UnitsOnOrder smallint NULL
)
CREATE TABLE dbo.Suppliers
(
Address nvarchar(60) NULL,
City nvarchar(15) NULL,
CompanyName nvarchar(40) NOT NULL,
ContactName nvarchar(30) NULL,
ContactTitle nvarchar(30) NULL,
Country nvarchar(15) NULL,
Fax nvarchar(24) NULL,
HomePage ntext NULL,
Phone nvarchar(24) NULL,
PostalCode nvarchar(10) NULL,
Region nvarchar(15) NULL,
SupplierID int NOT NULL
)
/*
* Add primary keys.
*/
ALTER TABLE dbo.Categories ADD
CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED
(
CategoryID
)
GO
ALTER TABLE dbo.Products ADD
CONSTRAINT PK_Products PRIMARY KEY CLUSTERED
(
ProductID
)
GO
ALTER TABLE dbo.Suppliers ADD
CONSTRAINT PK_Suppliers PRIMARY KEY CLUSTERED
(
SupplierID
)
GO
/*
* Add table indexes.
*/
ALTER TABLE dbo.Categories
ADD CONSTRAINT IX_Categories_CategoryName UNIQUE NONCLUSTERED
(
CategoryName
)
GO
CREATE INDEX IX_Products_CategoryID ON dbo.Products
(
CategoryID
)
GO
CREATE INDEX IX_Products_ProductName ON dbo.Products
(
ProductName
)
GO
CREATE INDEX IX_Products_SupplierID ON dbo.Products
(
SupplierID
)
GO
CREATE INDEX IX_Suppliers_CompanyName ON dbo.Suppliers
(
CompanyName
)
GO
CREATE INDEX IX_Suppliers_PostalCode ON dbo.Suppliers
(
PostalCode
)
GO
/*
* Add foreign keys.
*/
ALTER TABLE dbo.Products ADD
CONSTRAINT FK_Products_Categories_CategoryID FOREIGN KEY
(
CategoryID
)
REFERENCES Categories
(
CategoryID
)
GO
ALTER TABLE dbo.Products ADD
CONSTRAINT FK_Products_Suppliers_SupplierID FOREIGN KEY
(
SupplierID
)
REFERENCES Suppliers
(
SupplierID
)
GO
/*****************************************************************************
* Convert the database.
*****************************************************************************/
/*
* Drop foreign keys.
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'FK_Products_Categories_CategoryID') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE dbo.Products DROP CONSTRAINT
FK_Products_Categories_CategoryID
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'FK_Products_Suppliers_SupplierID') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE dbo.Products DROP CONSTRAINT
FK_Products_Suppliers_SupplierID
GO
/*
* Drop indexes.
*/
IF EXISTS (SELECT name FROM dbo.sysindexes WHERE name =
N'IX_Products_CategoryID')
DROP INDEX dbo.Products.IX_Products_CategoryID
GO
IF EXISTS (SELECT name FROM dbo.sysindexes WHERE name =
N'IX_Products_SupplierID')
DROP INDEX dbo.Products.IX_Products_SupplierID
GO
/*
* Drop primary keys.
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'PK_Categories') and OBJECTPROPERTY(id, N'IsPrimaryKey') =
1)
ALTER TABLE dbo.Categories DROP CONSTRAINT PK_Categories
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'PK_Products') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE dbo.Products DROP CONSTRAINT PK_Products
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'PK_Suppliers') and OBJECTPROPERTY(id, N'IsPrimaryKey') =
1)
ALTER TABLE dbo.Suppliers DROP CONSTRAINT PK_Suppliers
GO
/*
* Rename primary keys columns.
*/
EXECUTE sp_rename N'dbo.Categories.CategoryID', N'CategoryID_OLD',
'COLUMN'
GO
EXECUTE sp_rename N'dbo.Products.ProductID', N'ProductID_OLD',
'COLUMN'
GO
EXECUTE sp_rename N'dbo.Suppliers.SupplierID', N'SupplierID_OLD',
'COLUMN'
GO
/*
* Add ID and timestamp columns
*/
ALTER TABLE Categories ADD ID int IDENTITY (1, 1) NOT NULL
GO
ALTER TABLE Categories ADD RowTimestamp timestamp NOT NULL
GO
ALTER TABLE Products ADD ID int IDENTITY (1, 1) NOT NULL
GO
ALTER TABLE Products ADD RowTimestamp timestamp NOT NULL
GO
ALTER TABLE Suppliers ADD ID int IDENTITY (1, 1) NOT NULL
GO
ALTER TABLE Suppliers ADD RowTimestamp timestamp NOT NULL
GO
/*
* Rename old foreign key columns.
*/
EXECUTE sp_rename N'dbo.Products.CategoryID', N'CategoryID_OLD',
'COLUMN'
GO
EXECUTE sp_rename N'dbo.Products.SupplierID', N'SupplierID_OLD',
'COLUMN'
GO
/*
* Add new foreign key columns.
*/
ALTER TABLE Products ADD CategoriesID int NULL
GO
ALTER TABLE Products ADD SuppliersID int NULL
GO
/*
* Update new foreign key columns from the old values (OMITTED)
*/
/*
* Add new primary keys.
*/
ALTER TABLE dbo.Categories ADD
CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED
(
ID
)
GO
ALTER TABLE dbo.Products ADD
CONSTRAINT PK_Products PRIMARY KEY CLUSTERED
(
ID
)
GO
ALTER TABLE dbo.Suppliers ADD
CONSTRAINT PK_Suppliers PRIMARY KEY CLUSTERED
(
ID
)
GO
/*
* Add new foreign keys.
*/
ALTER TABLE dbo.Products ADD
CONSTRAINT FK_Products_Categories_CategoriesID FOREIGN KEY
(
CategoriesID
)
REFERENCES Categories
(
ID
)
GO
ALTER TABLE dbo.Products ADD
CONSTRAINT FK_Products_Suppliers_SuppliersID FOREIGN KEY
(
SuppliersID
)
REFERENCES Suppliers
(
ID
)
GO
/*
* Drop or rename old key columns (primary and foreign).
*/
ALTER TABLE dbo.Categories
DROP COLUMN CategoryID_OLD
GO
ALTER TABLE dbo.Products
DROP COLUMN CategoryID_OLD
GO
ALTER TABLE dbo.Products
DROP COLUMN ProductID_OLD
GO
ALTER TABLE dbo.Products
DROP COLUMN SupplierID_OLD
GO
ALTER TABLE dbo.Suppliers
DROP COLUMN SupplierID_OLD
GO
- Next message: Dejan Sarka: "Re: sql server alerts"
- Previous message: lwood: "Re: SQL Backup and Restore"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
