A script to create a relationship in SQL Server

Posted on 2009-02-21
Last Modified: 2012-05-06

I have been unable to find the sytax to create a relationship between two tables in SQL Server.  There is a good chance that i have a fundamental misunderstanding about relationships.

The relationship I need to create is not based on a FK.

I can create a foreign key relationship without issue.  And I can create a 'relationship' via the SQL Server Managemnt Studio by right clicking the table in design mode and choosing 'Relationships...'.  But i need to be able to script that type of relationship somehow.

Lastly, is there any difference between a Foreign Key relationship and a relationship that does not use a FK but a unique field.

Question by:agrogers
    LVL 142

    Accepted Solution

    let's explain several things:
    * a INDEX is just a structure beside the table data, making a "lookup" of a row based on 1 or several column values faster.
    * a UNIQUE INDEX is also nothing else than a INDEX combined with a UNIQUE constraint, to make sure each value is only present once in the table
    * a UNIQUE CONSTRAINT is the same than a UNIQUE INDEX: a index with unique constraint...
    * a PRIMARY KEY is the same as a UNIQUE CONSTRAINT.

    * a FOREIGN KEY is nothing else than a constraint that points to a UNIQUE INDEX/PRIMARY KEY/UNIQUE CONSTRAINT, usually on another table, it can be on the same table.
    LVL 5

    Assisted Solution

    On the point of getting the t-sql for something you have done in SSMS. You could create the relationship there and use Script Table from the context of that table to view the SQL that would create it.
    If not everything you need is included you may want to look at the Scripting in the Options.
    LVL 42

    Assisted Solution

    PK and FK scripts + Tables structure scriot will give you what you need
    Design A Database Using an Entity-Relationship Diagram
    Also you can try inner join in your sql syntax  

    LVL 68

    Assisted Solution

    >> * a PRIMARY KEY is the same as a UNIQUE CONSTRAINT. <<

    That is not technically correct, since:

    1) A Primary Key by default will always be clustered (unless a clus index already exists), but a unique constraint will not unless you explicitly tell SQL to cluster on that index.

    2) A PK cannot contain any NULL values; a UC can, as long as each set of index columns is not duplicated, including the null.  For example, if a PK is (colA, colB), you cannot insert (1, NULL).  If it's a UC, you can, and (NULL, 1) but you could not then insert another (1, NULL)/(NULL, 1).
    LVL 2

    Author Comment

    Hi guys. My appreciation for your responses is inversely proportional to the tardiness of this response.  I have now managed to do what i needed.  Part of the problem was SQLDMO giving me some unexpected results.  But all good now.  And so to assign points...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    SQL Query 18 64
    SQL Select Query problems 10 36
    Barracuda Bsckup Server 690 6 48
    SQL Round a percentage 2 13
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now