Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Create Table Script

Posted on 2011-10-14
4
Medium Priority
?
233 Views
Last Modified: 2012-06-27
Please help with the following script.  I created the first two tables without errors, but when I start executing the scripts with foreign key columns, their where errors with the referencial integrity.
Somebody help to reorg the foreign keys before I  execute the script again.  thanks


/* ********** Server Table ********* */
CREATE TABLE Server
(
    Server_Name             varchar (36)     NOT NULL,
        CONSTRAINT Server_PK PRIMARY KEY (Server_Name),
    OS_Edition              varchar (36)     NULL,
    OS_Service_Pack         varchar (36)     NULL,
    OS_Version              varchar (36)     NULL,
    Domain                  varchar (36)     NOT NULL,
    Location_ID             varchar (36)     NOT NULL,
    Physical                varchar (36)     NOT NULL,
    Priority                numeric (2)      NOT NULL,
    Primary_Contact         varchar (48)     NOT NULL,
    Secondary_Contact       varchar (48)     NULL,
    Tertiary_Contact        varchar (48)     NULL
)



/* ********** Vendor Table ********* */
CREATE TABLE Vendor
(
    Vendor_ID               varchar (36)     NOT NULL,
        CONSTRAINT Vendor_PK PRIMARY KEY (Vendor_ID),
    Company_Name            varchar (36)     NOT NULL,
    Address1                varchar (36)     NULL,
    Address2                varchar (36)     NULL,
    City                    varchar (36)     NULL,
    State                   varchar (36)     NULL,
    Zip                     varchar (10)     NULL,
    Country                 varchar (36)     NULL

)


/* ********** Contact Table ********* */
CREATE TABLE Contact
(
    Contact_ID              varchar (48)     NOT NULL,
        CONSTRAINT Contact_PK PRIMARY KEY (Contact_ID),
    Last_Name               varchar (36)     NOT NULL,
    First_Name              varchar (36)     NOT NULL,
    MI                      varchar (1)      NULL,
    Suffix                  varchar (3)      NULL,
    Vendor_ID               varchar (36)     NULL,
        CONSTRAINT FK_Vendor
            FOREIGN KEY (Vendor_ID)
            REFERENCES Vendor(Vendor_ID)
)



/* ********** Hardware Table ********* */
CREATE TABLE Hardware
(
    Hardware_ID             varchar (36)     NOT NULL,
        CONSTRAINT Hardware_PK PRIMARY KEY (Hardware_ID),
    Server_Name             varchar (36)     NULL,
        CONSTRAINT FK_Server
            FOREIGN KEY (Server_Name)
            REFERENCES Server(Server_Name),
    Manufacturer            varchar (36)     NULL,
    Serial_NO               varchar (36)     NULL,
    Pilot_ID                numeric (5)      NULL,
    Model                   varchar (36)     MULL,
    CPU                     varchar (36)     NULL,
    RAM                     varchar (36)     NULL
)



/* ********** Network Table ********* */
CREATE TABLE Network
(
    IP_Address              varchar (15)     NOT NULL,
        CONSTRAINT Network_PK PRIMARY KEY (IP_Address),
    Server_Name             varchar (36)     NULL,
        CONSTRAINT FK_Server
            FOREIGN KEY (Server_Name)
            REFERENCES Server(Server_Name),
    Type                    varchar (36)     NULL,
    URL                     varchar (36)     NULL,
    DNS                     varchar (36)     NULL
)



/* ********** Database_Server Table ********* */
CREATE TABLE Database_Server
(
    Database_Server_ID      varchar (48)     NOT NULL,
        CONSTRAINT Database_Server_PK PRIMARY KEY (Database_Server_ID),
    Database_Engine         varchar (36)     NOT NULL,
    Database_Version        varchar (36)     NOT NULL,
    Database_Edition        varchar (36)     NOT NULL,
    Database_Instance       varchar (36)     NOT NULL,
    Server_Name             varchar (36)     NOT NULL,
        CONSTRAINT FK_Server
            FOREIGN KEY (Server_Name)
            REFERENCES Server(Server_Name)
)



/* ********** Database Table ********* */
CREATE TABLE DB
(
    Database_ID             varchar (36)     NOT NULL,
        CONSTRAINT DB_PK PRIMARY KEY (Database_ID),
    Database_Name           varchar (36)     NOT NULL,
    Database_Server_ID      varchar (48)     NOT NULL,
        CONSTRAINT FK_Database_Server
            FOREIGN KEY (Database_Server_ID)
            REFERENCES Database_Server(Database_Server_ID),
    Primary_Contact         varchar (48)     NULL,
        CONSTRAINT FK_Contact
            FOREIGN KEY (Primary_Contact)
            REFERENCES Contact(Contact_ID),
    Secondary_Contact       varchar (48)     NULL,
        CONSTRAINT FK_Contact
            FOREIGN KEY (Secondary_Contact)
            REFERENCES Contact(Contact_ID),
    Tertiary_Contact        varchar (48)     NULL,
        CONSTRAINT FK_Contact
            FOREIGN KEY (Tertiary_Contact)
            REFERENCES Contact(Contact_ID)
)



/* ********** Location Table ********* */
CREATE TABLE Location
(
    Location_ID                varchar (36)     NOT NULL,
        CONSTRAINT Location_PK PRIMARY KEY (Location_ID),
    Site                    varchar (36)     NOT NULL,
    Row                     varchar (36)     NULL,
    Rack                    numeric (2)      NULL,
    Bay                     varchar (5)      NULL
)



/* ********** Client Table ********* */
CREATE TABLE Client
(
    Client_ID               varchar (36)     NOT NULL,
        CONSTRAINT Client_PK PRIMARY KEY (Client_ID),
    Client_Name             varchar (36)     NOT NULL,
    Client_Notes            memo (400)       NULL
)



/* ********** Application Table ********* */
CREATE TABLE Application
(
    Application_Name        varchar (36)     NOT NULL,
        CONSTRAINT Application_PK PRIMARY KEY (Application_Name),
    Description             varchar (36)     NULL,
    Server_Name             varchar (36)     NULL,
        CONSTRAINT FK_Server
            FOREIGN KEY (Server_Name)
            REFERENCES Server(Server_Name),
    Database_ID             varchar (36)     NULL,
    Primary_Contact         varchar (48)     NOT NULL,
        CONSTRAINT FK_Contact
            FOREIGN KEY (primary_Contact)
            REFERENCES Contact(Contact_ID),
    Secondary_Contact       varchar (48)     NULL,
        CONSTRAINT FK_Contact
            FOREIGN KEY (Secondary_Contact)
            REFERENCES Contact(Contact_ID),
    Tertiary_Contact        varchar (48)     NULL,
        CONSTRAINT FK_Contact
            FOREIGN KEY (Tertiary_Contact)
            REFERENCES Contact(Contact_ID)
)



/* ********** App_Client Table ********* */
CREATE TABLE App_Client
(
    App_Client_ID           varchar (36)     NOT NULL,
        CONSTRAINT App_Client_PK PRIMARY KEY (App_Client_ID),
    Application_Name        varchar (36)     NOT NULL,
        CONSTRAINT FK_App_Client
            FOREIGN KEY (Application_Name)
            REFERENCES Application(Application_Name),
    Client_ID               varchar (36)     NULL,
        CONSTRAINT FK_App_Client
            FOREIGN KEY (Client_ID)
            REFERENCES Client(Client_ID)
)




/* ********** Alter Server Table ********* */
ALTER TABLE Server
        CONSTRAINT FK_Contact
            FOREIGN KEY (primary_Contact)
            REFERENCES Contact(Contact_ID),
        CONSTRAINT FK_Contact
            FOREIGN KEY (Secondary_Contact)
            REFERENCES Contact(Contact_ID),
        CONSTRAINT FK_Contact
            FOREIGN KEY (Tertiary_Contact)
            REFERENCES Contact(Contact_ID),
        CONSTRAINT FK_Location
            FOREIGN KEY (Location_ID)
            REFERENCES Location(Location_ID)
;
0
Comment
Question by:Favorable
4 Comments
 
LVL 2

Expert Comment

by:DataCruncher
ID: 36969678
Could you paste in here the exact error you are receiving when trying to execute the CREATE TABLE statement for your 3rd table (Contact) ?
0
 

Expert Comment

by:Maximus85
ID: 36969732
Try this with te primary key


/* ********** Server Table ********* */
CREATE TABLE Server
(
    Server_Name             varchar (36) CONSTRAINT Server_PK PRIMARY KEY,
    OS_Edition              varchar (36)     NULL,
    OS_Service_Pack         varchar (36)     NULL,
    OS_Version              varchar (36)     NULL,
    Domain                  varchar (36)     NOT NULL,
    Location_ID             varchar (36)     NOT NULL,
    Physical                varchar (36)     NOT NULL,
    Priority                numeric (2)      NOT NULL,
    Primary_Contact         varchar (48)     NOT NULL,
    Secondary_Contact       varchar (48)     NULL,
    Tertiary_Contact        varchar (48)     NULL
)

Try this with foreing key

/* ********** Hardware Table ********* */
CREATE TABLE Hardware
(
    Hardware_ID             varchar (36) CONSTRAINT Hardware_PK PRIMARY KEY,
    Server_Name             varchar (36) CONSTRAINT FK_Server REFERENCES Server (Server_Name),
    Manufacturer            varchar (36)     NULL,
    Serial_NO               varchar (36)     NULL,
    Pilot_ID                numeric (5)      NULL,
    Model                   varchar (36)     MULL,
    CPU                     varchar (36)     NULL,
    RAM                     varchar (36)     NULL
)
0
 
LVL 6

Accepted Solution

by:
regevha earned 2000 total points
ID: 36969749
Attached the fixed script (please note I replaced a "memo" field with "varchar") SQLQuery1.sql
0
 

Author Closing Comment

by:Favorable
ID: 36989954
Thank you very much!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question