none
Creating sql server tables with more then one primary key RRS feed

  • Question

  • When I create a table that has one primary key. Can I set a 2nd column with another primary key. The idea is to delete records from a sql server database that has related records in other tables.

    Let's say I have a table called "bases" and a 2nd table called "maps".

    Each base is related to one game where each base is related to records in the table maps. In the table maps I have a record called "baseid". This Id related to the bases I'd. So making deleting records easy.

    So If I delete a game. Then it deletes the bases. for that game, It needs to delete the records for that base in maps. If anyone can under stand what I'm saying.

    Each game will have an ID number that needs to be added to each table "base". as like the table "maps"

    So I need to add a 2nd primary key added to "bases" called "gameid" and I need to change the "based" in maps to a primary key.

    Anyone know how to create a table with 2 primary keys.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 24, 2019 12:22 PM

Answers

  • In regards to can it be done in C#, yes, any valid T-SQL can be done in C#. The real question should be "should it be done in C#", yes if no other alternatives while an alternative is to use SSMS.

    A rough example will look like this for C#.

    public void demo()
    {
        using (var cn = new SqlConnection {ConnectionString = ConnectionString})
        {
            using (var cmd = new SqlCommand() {Connection = cn})
            {
                var sqlStatement = "ALTER TABLE dbo.bases DROP CONSTRAINT FK_T1_bases;";
    
                cmd.CommandText = sqlStatement;
    
    
    
                cn.Open();
                cmd.ExecuteNonQuery();
    
    
                sqlStatement = "ALTER TABLE dbo.bases ADD CONSTRAINT FK_T1_bases_Cascade " +
                               "FOREIGN KEY (ID) " +
                               "REFERENCES dbo.bases(ID) ON DELETE CASCADE";
    
                cmd.ExecuteNonQuery();
            }
        }
    }

    Documentation DROP Constraint  ALTER TABLE  References Keys

    Going back to the code above, I don't have time to take you through every single step to do this in code to exactly match your schema plus I fully recommend using SSMS.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, March 24, 2019 3:05 PM
    Moderator

All replies

  • Hello,

    This can be done in SSMS (SQL-Server Management Studio) by setting a "Delete Rule" to do a cascading delete as per the image below.

    1. Select the table in Object Explorer.
    2. Right click, select "Design"
    3. Right click in the designer and select relationships
    4. Select the relationship
    5. Click on INSERT And Update specifics
    6. Select cascade in the Delete rule.
    7. Press Close.
    8. Save

    See also


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, March 24, 2019 1:23 PM
    Moderator
  • Karen, Thank you. That tells me that it can be done. Can it be done in c#. I thought I posted in the right forum.

    using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,basename char(50),shortname char(10),gameid int NOT NULL);", conn))
                                { command2.ExecuteNonQuery(); }
    

    As you can see this creates a table called bases. On the end of this line you see "gameid". I need to make this a primary key so that the games matching records can be deleted.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 24, 2019 2:12 PM
  • In regards to can it be done in C#, yes, any valid T-SQL can be done in C#. The real question should be "should it be done in C#", yes if no other alternatives while an alternative is to use SSMS.

    A rough example will look like this for C#.

    public void demo()
    {
        using (var cn = new SqlConnection {ConnectionString = ConnectionString})
        {
            using (var cmd = new SqlCommand() {Connection = cn})
            {
                var sqlStatement = "ALTER TABLE dbo.bases DROP CONSTRAINT FK_T1_bases;";
    
                cmd.CommandText = sqlStatement;
    
    
    
                cn.Open();
                cmd.ExecuteNonQuery();
    
    
                sqlStatement = "ALTER TABLE dbo.bases ADD CONSTRAINT FK_T1_bases_Cascade " +
                               "FOREIGN KEY (ID) " +
                               "REFERENCES dbo.bases(ID) ON DELETE CASCADE";
    
                cmd.ExecuteNonQuery();
            }
        }
    }

    Documentation DROP Constraint  ALTER TABLE  References Keys

    Going back to the code above, I don't have time to take you through every single step to do this in code to exactly match your schema plus I fully recommend using SSMS.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, March 24, 2019 3:05 PM
    Moderator
  • FYI any table can have ONE Primary Key, but other ["alternate"] keys can be added to suit app usage.

    As noted already, you can execute any TSQL statements to perform any metadata changes and/or DML.

    I recommend you take a look at SMO ("SqlServer Management Objects") too before deciding how to proceed

    https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/overview-smo?view=sql-server-2017

    HTH

    Dick


    DickInKent

    Sunday, March 24, 2019 3:15 PM
  • Thanks Karen. You don't seem to under stand that not everyone will want to download and install SSMS. Or take the time out to learn some thing new. My idea is so that they don't have too. It looks like your using ALTER TABLE to change the "gameid" to a foreign key. am I right. anyway when I get to that part of the code I'll have to play with it and see if there's another way.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 24, 2019 5:09 PM
  • Hi! Dick, Thanks for the info. It's just Karen wanted me to do the same thing. It's just that SMO is too advance for me. I never used it before. So I would be barking up the wrong code. lol, anyway when I get this part completed of my app and if I paste the full code for this part. and is anyone has the time. They are well come to make the changes to it so that SMO can be used.

    Joe


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Sunday, March 24, 2019 5:13 PM