Sense-checking request

weejas · #1 (**permalink**) 01-20-12, 06:00

Morning all!

I'm attempting to use SQL statement to create tables within a new database, and I'm somewhat lacking in experience for this. Normally I'd simply create them through the GUI, but I want to get the hang of DDL statements.

I have written what I think are scripts to create one table with a primary key, create a unique index on it, and then create another table with a primary key and constrain two columns in the second table to the non-primary unique index in the first. I have done this outside of a SQL editor as I would like to get it right first! Would anyone be willing to cast their eye over it and let me know what I've done wrong? TIA!

Code:

Use SKUTracking

CREATE TABLE
	SKUTracking.dbo.tblOraUnits
	(
	OraUnitName nvarchar(25) CONSTRAINT PK_OraUnits PRIMARY KEY
,	OraUnit nvarchar(3) NOT NULL
,	OraUnitClass nvarchar(10) NOT NULL
,	OraBaseFlag nvarchar(1) NOT NULL
,	OraConvFactorToBase decimal(20, 10) NOT NULL
	)

CREATE UNIQUE NONCLUSTERED INDEX
	idxOraUnits
ON
	tblOraUnits
	(
	OraUnit ASC
	)

CREATE TABLE
	SKUTracking.dbo.tblOraItems
	(
	OraItemNo nvarchar(32) CONSTRAINT PK_OraItems PRIMARY KEY
,	OraDescription nvarchar(70) NOT NULL
,	OraUnit nvarchar(4) CONSTRAINT NOT NULL FOREIGN KEY REFERENCES tblOraUnits(OraUnit)
,	OraDualFlag integer NOT NULL
,	OraDualUnit nvarchar(4) CONSTRAINT NULL FOREIGN KEY REFERENCES tblOraUnits(OraUnit)
	)

For reference, this is on SQL Server 2000.

healdem · #2 (**permalink**) 01-20-12, 06:41

Quote:

Originally Posted by weejas

Morning all!
....I have written what I think are scripts to create one table with a primary key, create a unique index on it, and then create another table with a primary key and constrain two columns in the second table to the non-primary unique index in the first. I have done this outside of a SQL editor as I would like to get it right first! Would anyone be willing to cast their eye over it and let me know what I've done wrong? TIA!

For reference, this is on SQL Server 2000.

why not just run the SQL and see what happens. providing the tables are not used / referenced by anyone else what the harm in running the SQL. furthermore assuming you have errors in the SQL you will learn a heck of a lot more by runnign and then debugging your own stuff

weejas · #3 (**permalink**) 01-20-12, 06:45

Good point.

The main reason is that I have about 10 tables to create, and I would really like to write one script and create them all in one go...

weejas · #4 (**permalink**) 01-20-12, 07:43

Okay, so taking healdem's advice, I've plugged the above statements (one at a time) into SQL Query Analyzer.
The first two worked, and so I no have a table with a primary key and a unique index in my database. Woot.
When I tried the third statement, I got the following error:

Code:

Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'NOT'.

Looking at the format of the lines that worked, I switched the NULL/NOT NULL keywords with the CONSTRAINT keywords on their respective lines. This yielded a very slightly different error message:

Code:

Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'FOREIGN'.

If I comment out the code from "CONSTRAINT" to the end of the line, SQL Analyzer accepts it. Do I need to create the table and then apply the constraint?

Wim · #5 (**permalink**) 01-20-12, 08:56

This is more how I would write it.

There is no need to repeat the database name (SKUTracking), you already defined it at the start. This way you only have to change it in one place when you want to do things in or your development or staging or test or production database.

I put the PRIMARY KEY declaration separate from the column declaration. If you have a PK with more than 1 column, you have to do it in this way anyway.

I put the FOREIGN KEY definitions outside the table definition. In a large database, you otherwise end up wasting more and more time getting the sequence of table definitions in the right order (parent before child).

OraUnitName / OraUnit:Take the shortest column as the PK when you can choose between multiple candidate keys.
I noticed you sometimes used NVARCHAR(4) or NVARCHAR(3) for OraUnit.
<flameShieldUp>I also use Id INTEGER IDENTITY(1, 1) NOT NULL as PK for each and every table.<flameShieldDown>.

If OraUnits is a lookup table, don't use IDENTITY(1,1). Assign the values of Id explicit, so can repeat it on your different databases (test, dev, prod, .), with IDENTITY keeping those synchronised can be a pain.

Code:

Use SKUTracking

CREATE TABLE dbo.OraUnits(
	Id		INTEGER	IDENTITY(1, 1)	NOT NULL,
	OraUnit		nvarchar(3)		NOT NULL,
	OraUnitName	nvarchar(25)		NOT NULL,
	OraUnitClass	nvarchar(10)		NOT NULL,
	OraBaseFlag	nvarchar(1)		NOT NULL,
	OraConvFactorToBase	decimal(20, 10)	NOT NULL,
	CONSTRAINT PK_OraUnits PRIMARY KEY (Id)
	)

CREATE UNIQUE NONCLUSTERED INDEX ui_OraUnit ON dbo.OraUnits (OraUnit ASC)
CREATE UNIQUE NONCLUSTERED INDEX ui_OraUnitName ON dbo.OraUnits (OraUnitName ASC)

CREATE TABLE dbo.OraItems(
	Id		INTEGER	IDENTITY(1, 1)	NOT NULL,
	OraItemNo	nvarchar(32)		NOT NULL,
	OraDescription	nvarchar(70)		NOT NULL,
	OraUnitId	integer			NOT NULL ,
	OraDualFlag	integer			NOT NULL,
	OraDualUnitId	nvarchar(3)		NULL,
	CONSTRAINT PK_OraItems PRIMARY KEY (Id)
	)

CREATE UNIQUE NONCLUSTERED INDEX ui_OraItemNo ON dbo.OraItems (OraItemNo ASC)

ALTER TABLE dbo.tblOraItems
	ADD CONSTRAINT FK_tblOraItems_OraUnit FOREIGN KEY (OraUnitId)
	REFERENCES tblOraUnits(Id)
	
ALTER TABLE dbo.tblOraItems
	ADD CONSTRAINT FK_tblOraItems_OraDualUnit FOREIGN KEY (OraDualUnitId)
	REFERENCES tblOraUnits(Id)

weejas · #6 (**permalink**) 01-20-12, 09:22

Thanks, Wim!

I will take heed of your comment about setting constraints after creating the tables. I get so used to populating tables in the right order that I've started trying to create them that way, too!

I do not want to go the route of creating a column purely to be the PK. The data for this database will come from three different ERP systems, and they each have their own approach to (and opinion of) RI. The model that I've drafted will make use of this.

Regarding tblOraUnits - I have based the design on that of the source table. The Oracle programmers decided that a 25-character key was better than a 3-character key, even though they must both be unique for the application to work. Similarly, the column definitions for this and tblOraItems match the inconsistencies within Oracle - the units columns in the items table are set to 4 characters, even though the referring column is only 3!

Have another

!

Wim · #7 (**permalink**) 01-20-12, 09:54

Quote:

I do not want to go the route of creating a column purely to be the PK. The data for this database will come from three different ERP systems, and they each have their own approach to (and opinion of) RI. The model that I've drafted will make use of this.

Never trust a PK that you cannot control. This is the top reason why you should supply your own pk in this case, to shield your system from whatever choices / changes they may make.

Quote:

Have another

!

Thanks. That makes 13 now!
It's almost worth learning to drink beer.

weejas · #8 (**permalink**) 01-20-12, 10:10

Quote:

Never trust a PK that you cannot control. This is the top reason why you should supply your own pk in this case, to shield your system from whatever choices / changes they may make.

These ones I trust - otherwise, the business would have collapsed years ago! :P
They are either automatically generated by one system or another, or are set very much in stone and cannot change without creating oodles of trouble...

Internet Services

Web Development

Digital Marketing

Consumer Tech