Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unanswered: Sense-checking request

    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by weejas View Post
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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)
    Last edited by Wim; 01-20-12 at 10:00.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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 !
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    Have another !
    Thanks. That makes 13 now!
    It's almost worth learning to drink beer.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •