Database testing : Check primary key null values

Null values should not be allowed for the Primary key column.

Language: Java

Framework: JUnit using JDBC

copy
1import java.sql.Connection;2import java.sql.DriverManager;3import java.sql.ResultSet;4import java.sql.SQLException;5import java.sql.Statement;6import org.junit.Assert;7import org.junit.BeforeClass;8import org.junit.Test;910public class DatabaseTest {11 12 private static Connection con;13 private static Statement stmt;14 15 @BeforeClass16 public static void setUp() throws ClassNotFoundException, SQLException {17 Class.forName("com.mysql.jdbc.Driver");18 con = DriverManager.getConnection("jdbc:mysql:/​/​localhost:3306/​testdb", "root", "password");19 stmt = con.createStatement();20 }21 22 @Test23 public void testPrimaryKeyNotNull() throws SQLException {24 ResultSet rs = stmt.executeQuery("SELECT * FROM sample_table WHERE primary_key_column IS NULL;");25 while(rs.next()) {26 Assert.fail("Primary key column should not allow null values.");27 }28 }29 30 /​/​ Uncomment the following code to connect to remote client with desired capabilities31 32 /​*33 @Test34 public void testPrimaryKeyNotNullRemote() throws SQLException {35 DesiredCapabilities capabilities = new DesiredCapabilities();36 capabilities.setPlatform(Platform.LINUX);37 capabilities.setBrowserName("chrome");38 WebDriver driver = new RemoteWebDriver(new URL("http:/​/​localhost:4444/​wd/​hub"), capabilities);39 driver.get("http:/​/​localhost:8080/​testwebapp");40 /​/​ Perform database test41 ResultSet rs = stmt.executeQuery("SELECT * FROM sample_table WHERE primary_key_column IS NULL;");42 while(rs.next()) {43 Assert.fail("Primary key column should not allow null values.");44 }45 driver.quit();46 }47 */​48 49 /​/​ Assumptions:50 /​/​ 1. The name of the primary key column is 'primary_key_column'51 /​/​ 2. The database used is MySQL and is hosted on local machine, with default port and login credentials52 /​/​ 3. The table name is 'sample_table'53 /​/​ 4. The test will fail if any null value is found in the primary key column54}

Language: Python

Framework: Pytest

copy
1Here is the code snippet for the given test case using pytest framework in Python:23```python4import pytest5import psycopg2 # Importing PostgreSQL driver for Python67# Test data8database_name = "my_database"9table_name = "my_table"10primary_key_column = "id"1112# Fixture to create a database connection13@pytest.fixture14def db_connection():15 conn = psycopg2.connect(database=database_name, user="postgres", password="my_password", host="127.0.0.1", port="5432")16 yield conn17 conn.close()1819# Test to check primary key null values20def test_primary_key_null_values(db_connection):21 cursor = db_connection.cursor()22 cursor.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({primary_key_column} SERIAL PRIMARY KEY, name VARCHAR(255));")23 24 # Test case with null primary key value25 with pytest.raises(Exception):26 cursor.execute(f"INSERT INTO {table_name} (name) VALUES ('my_name');")27 db_connection.commit()28 29 cursor.execute(f"DROP TABLE {table_name};")30 db_connection.commit()3132# Code to use local driver33'''34from selenium import webdriver35driver = webdriver.Firefox()36'''3738# Code to connect to remote client with desired capabilities39'''40from selenium import webdriver41from selenium.webdriver.common.desired_capabilities import DesiredCapabilities4243desired_cap = {44 'browser': 'Chrome',45 'browser_version': '91.0',46 'os': 'Windows',47 'os_version': '10'48}4950driver = webdriver.Remote(51 command_executor='http:/​/​localhost:4444/​wd/​hub',52 desired_capabilities=desired_cap)53'''54```5556Assumptions:57- PostgreSQL is being used as the database system.58- The PostgreSQL server is hosted on the same machine running the test script with default port 5432.59- A database named "my_database" already exists and the user "postgres" has necessary privileges to connect to it and create/​drop tables.60- The primary key column has a SERIAL data type, which automatically generates unique integer values for each new row inserted.61- Only one column (name) is present in the table besides primary key.62- The web driver to use is Firefox, and it's already installed in the system.63- Alternatively, a remote Selenium Grid server is already set up and running on the host at the specified URL with a node having the desired capabilities.

Disclaimer: Following code snippets and related information have been sourced from GitHub and/or generated using AI code generation tools. LambdaTest takes no responsibility in the accuracy of the code and is not liable for any damages.

Accelerate Your Automation Test Cycles With LambdaTest

Leverage LambdaTest’s cloud-based platform to execute your automation tests in parallel and trim down your test execution time significantly. Your first 100 automation testing minutes are on us.

Try LambdaTest

Power Your Software Testing with AI and cloud

Test Intelligently and ship faster. Deliver unparalleled digital experiences for real world enterprises.

Start Free Testing