CODEWITHSUNDEEP
sqlsql-servernull
Weston Sapusek
Weston Sapusek

Reputation: 21

Adding NULL values in a table with a composite primary key

I need to insert this table into SQL management Studio with a composite primary key consisting of Job_Title and Job_Begin. The problem is, both of those columns have 2 rows where they are NULL. The composite primary key takes care of this on paper but the program won't let me have a primary key that accepts NULL values, even when there is a composite primary Key. How do I get around this? Code for the table so far is as follows. Please note it won't work right now. Just changing "NOT NULL" to "NULL" for job_title and job_begin will make it so that the program won't allow me to define those columns as primary keys.

    CREATE TABLE [dbo].[Jobs]
    (
     [job_title] VARCHAR(255) NOT NULL ,
     [job_begin] VARCHAR(255) NOT NULL ,
     [proj_no]   VARCHAR(255) NOT NULL REFERENCES Projects (proj_no),
     [emp_no]    FLOAT NOT NULL REFERENCES Employees (emp_no),
     PRIMARY KEY([job_title], [job_begin])

    );
    GO

INSERT INTO [dbo].[Jobs]([job_title], [job_begin], [proj_no], [emp_no])
VALUES('Driver', '2015-06-01', 'p2', '18316'),
       (NULL, '2015-08-01', 'p1', '28559'),
       ('Engineer', '2016-02-01', 'p2', '28559'),
       ('Analyst', '2015-10-15', 'p3', '2581'),
       ('Manager', '2015-04-15', 'p1', '9031'),
       ('Engineer', '2014-11-15', 'p3', '9031'),
       ('Engineer', '2015-01-04', 'p1', '29346'),
       (NULL, '2014-12-15', 'p2', '29346'),
       ('Analyst', '2015-09-25', 'p11', '30606'),
       ('Programmer', NULL, 'p20', '30606'),
       ('Analyst', '2014-10-01', 'p1', '10102'),
       ('Manager', '2012-01-01', 'p3', '10102'),
       ('Engineer', '2015-02-15', 'p2', '25348'),
       ('QA', NULL, 'p11', '5500'),
       ('Programmer', '2016-11-09', 'p14', '5500'),
       ('QA', '2016-03-22', 'p2', '5500'),
       ('Manager', '2013-01-18', 'p20', '5500');

enter image description here

Upvotes: 0

Views: 2506

Answers (3)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

You should normalize your tables

  1. Move your NULL columns on their own tables
  2. Use IDs of those columns to map your job title, date and rest of the columns for example:

    CREATE TABLE [dbo].[job_title] 
        (
         [job_title_id] int identity(1,1) NOT NULL,
         [name] VARCHAR(255) NOT NULL, -- you can use 'Unknown' instead of NULL in your query
         PRIMARY KEY([job_title_id])
        )
        GO
    CREATE TABLE [dbo].[job_begin]
        (
         [job_begin_id] int identity(1,1) NOT NULL,
         [name] VARCHAR(255) NOT NULL,
         PRIMARY KEY([job_begin_id])

        )
        GO
     CREATE TABLE [dbo].[Jobs]
        (
         [job_title_id] int NOT NULL references job_title(job_title_id),
         [job_begin_id]  int NOT NULL references job_begin(job_begin_id),
         [proj_no]   VARCHAR(255) NOT NULL REFERENCES Projects (proj_no),
         [emp_no]    FLOAT NOT NULL REFERENCES Employees (emp_no),
         PRIMARY KEY([job_title_id], [job_begin_id])

        )
        GO

    INSERT INTO [dbo].[job_title](name)
    VALUES('Driver')
    GO;

    INSERT INTO [dbo].[job_begin](name)
    VALUES('2015-06-01')
    GO;

    INSERT INTO [dbo].[Jobs]([job_title_id], [job_begin_id], [proj_no], [emp_no])
    VALUES(1, 1, 'p2', '18316'),
    ...

But as mentioned before, if you don't have your combination of job_title and job_begin unique, you will get violation of primary key exception. In that case you can add additional column to your primary key - even an identity(1,1) column to make it unique.

Upvotes: 1

Bohemian
Bohemian

Reputation: 425063

Columns of primary keys can't be null, so just use a blank (empty string, ie '') for "null" values.

While it mightn't be popular with "purists", it solves your problem and it's not a big deal. Just move on.

Upvotes: 1

lolo4
lolo4

Reputation: 119

You cannot have NULL values in a column when using the column to make up the primary key.

What you could do is maybe just replace the NULL values with a blank value.

Just be careful as each row must be unique so you can't have more than one row with a blank value in both the job_title and job_begin columns, or more than one row with a blank value in the job_title column and the same date in the job_begin column.

Upvotes: 0

Related Questions